使用没有PK的另一个表的值更新表

[英]Update table with values of another table without PK

本文翻译自 Shiju Shaji 查看原文 2016/03/03 39 sql-server-2012/ tsql/ sql-server

I have two tables

我有两张桌子

+-------+----+
| name  | id |
+-------+----+
| < -5  |  1 | 
| -2    |  5 |
| -1    |  6 |
| 0     |  7 |
| + 1   |  8 |
| + 3   |  9 |
| + 5   | 10 |
| > 60  | 17 |
+-------+----+
Table 2: 
+------------+------------+-------------------+-------------------+
| diff_date1 | diff_date2 | variance_id_date1 | variance_id_date2 |
+------------+------------+-------------------+-------------------+
|          0 |          0 |                 0 |                 0 |
|        -21 |        -21 |                 0 |                 0 |
|          5 |          5 |                 0 |                 0 |
|         58 |         58 |                 0 |                 0 |
|          4 |          4 |                 0 |                 0 |
+------------+------------+-------------------+-------------------+

Update variance_id_date1 and variance_id_date2 in Table 2 with the ID from table 1

使用表1中的ID更新表2中的variance_id_date1和variance_id_date2

Expected OutPut

期待OutPut

+------------+------------+-------------------+-------------------+
| diff_date1 | diff_date2 | variance_id_date1 | variance_id_date2 |
+------------+------------+-------------------+-------------------+
|          0 |          0 |                 7 |                 7 |
|        -21 |        -21 |                 1 |                 1 |
|          5 |          5 |                10 |                10 |
|         58 |         58 |                10 |                10 |
|          4 |          4 |                 9 |                 9 |
+------------+------------+-------------------+-------------------+

I don't want to hard code the values.

我不想硬编码值。

My try

我的尝试

update @table1
 set variance_id_date1 = (select id from  @table where name =  cast(diff_date1 as varchar) )
 ,variance_id_date1 = (select id from  @table where name between  cast(diff_date1 as varchar) and  )

2 个解决方案

#1

here is my suggestion:

这是我的建议：

+-------+-------+----+
| start | end   | id |
+-------+-------+----+
| NULL  | -5    |  1 |
| -4    | -2    |  5 |
| -1    | -1    |  6 |
| 0     | 0     |  7 |
| 1     | 2     |  8 |
| 3     | 4     |  9 |
| 5     | 60    | 10 |
| 61    | NULL  | 17 |
+-------+-------+----+

then simply join it with appropriate NULL handling

然后简单地用适当的NULL处理加入它

#2

The select script with sample output:

带有示例输出的select脚本：

SELECT DISTINCT a.diff_date1
                ,CASE WHEN b.Equal IS NOT NULL THEN Equal 
                 WHEN c.Greater IS NOT NULL THEN c.Greater 
                 ELSE d.Less END as variance_id_date1_Up
FROM [mASter].[dbo].[tableName2] AS a LEFT OUTER JOIN 
 (SELECT [diff_date1]
 ,CASE WHEN ISNUMERIC(REPLACE(REPLACE(b.name  ,'+',''),' ','')) = 1 AND CAST([diff_date1] AS int) = CAST(REPLACE(REPLACE(b.name  ,'+',''),' ','') AS int) then b.id 
  ELSE NULL  END AS [Equal]
  FROM [mASter].[dbo].[tableName2] AS a CROSS JOIN [mASter].[dbo].[tableName] AS b) AS b
  on a.diff_date1 =b.diff_date1 AND b.Equal IS NOT NULL 
  LEFT OUTER JOIN 
  (SELECT [diff_date1]
  ,Max(CAST(CASE WHEN ISNUMERIC(REPLACE(REPLACE(REPLACE(b.name  ,'>',''),' ',''),'+','')) = 1 AND CAST([diff_date1] AS int) > REPLACE(REPLACE(REPLACE(b.name  ,'>',''),' ',''),'+','') then b.id ELSE NULL END AS int)) AS [Greater]
  FROM [mASter].[dbo].[tableName2] AS a CROSS JOIN [mASter].[dbo].[tableName] AS b GROUP BY [diff_date1]) AS c
  on a.diff_date1 = c.diff_date1 AND c.Greater IS NOT NULL AND b.diff_date1 IS NULL  
  LEFT OUTER JOIN 
  (SELECT [diff_date1]
  ,Min(CAST(CASE WHEN ISNUMERIC(REPLACE(REPLACE(b.name  ,'<',''),' ','')) = 1 AND CAST([diff_date1] AS int) < CAST(REPLACE(REPLACE(b.name  ,'<',''),' ','') AS int) then b.id ELSE NULL  END AS int) )AS [Less]
  FROM [mASter].[dbo].[tableName2] AS a CROSS JOIN [mASter].[dbo].[tableName] AS b GROUP BY [diff_date1]) AS d
  on a.diff_date1 =d.diff_date1 AND b.diff_date1 IS NULL AND c.diff_date1 IS NULL

Sample output:

样本输出：

   diff_date1   variance_id_date1
    0                 7
    -21               1
    4                 9
    5                 10
    58                10

Update Script:

更新脚本：

Update [tableName2] set variance_id_date1 = variance_id_date1_Up from(
SELECT DISTINCT a.diff_date1
                ,CASE WHEN b.Equal IS NOT NULL THEN Equal 
                 WHEN c.Greater IS NOT NULL THEN c.Greater 
                 ELSE d.Less END as variance_id_date1_Up
FROM [mASter].[dbo].[tableName2] AS a LEFT OUTER JOIN 
 (SELECT [diff_date1]
 ,CASE WHEN ISNUMERIC(REPLACE(REPLACE(b.name  ,'+',''),' ','')) = 1 AND CAST([diff_date1] AS int) = CAST(REPLACE(REPLACE(b.name  ,'+',''),' ','') AS int) then b.id 
  ELSE NULL  END AS [Equal]
  FROM [mASter].[dbo].[tableName2] AS a CROSS JOIN [mASter].[dbo].[tableName] AS b) AS b
  on a.diff_date1 =b.diff_date1 AND b.Equal IS NOT NULL 
  LEFT OUTER JOIN 
  (SELECT [diff_date1]
  ,Max(CAST(CASE WHEN ISNUMERIC(REPLACE(REPLACE(REPLACE(b.name  ,'>',''),' ',''),'+','')) = 1 AND CAST([diff_date1] AS int) > REPLACE(REPLACE(REPLACE(b.name  ,'>',''),' ',''),'+','') then b.id ELSE NULL END AS int)) AS [Greater]
  FROM [mASter].[dbo].[tableName2] AS a CROSS JOIN [mASter].[dbo].[tableName] AS b GROUP BY [diff_date1]) AS c
  on a.diff_date1 = c.diff_date1 AND c.Greater IS NOT NULL AND b.diff_date1 IS NULL  
  LEFT OUTER JOIN 
  (SELECT [diff_date1]
  ,Min(CAST(CASE WHEN ISNUMERIC(REPLACE(REPLACE(b.name  ,'<',''),' ','')) = 1 AND CAST([diff_date1] AS int) < CAST(REPLACE(REPLACE(b.name  ,'<',''),' ','') AS int) then b.id ELSE NULL  END AS int) )AS [Less]
  FROM [mASter].[dbo].[tableName2] AS a CROSS JOIN [mASter].[dbo].[tableName] AS b GROUP BY [diff_date1]) AS d
  on a.diff_date1 =d.diff_date1 AND b.diff_date1 IS NULL AND c.diff_date1 IS NULL) AS INPUT
  where [tableName2].diff_date1 = INPUT.diff_date1

Remember to change the table name or columns name.

请记住更改表名称或列名称。

注意！

本站翻译的文章，版权归属于本站，未经许可禁止转摘，转摘请注明本文地址：http://www.silva-art.net/blog/2016/03/03/ee4105a8b71b09fb7f163e865b6ef5b8.html。

猜您在找

使用另一个表中的值更新表 - Update table with values from another table 使用另一个的count（*）值更新大表 - Update large table with count(*) values from another 使用另一个（PHP）中的值更新MySQL表 - Update MySQL table using values in another (PHP) 使用另一个表中的值更新一个表中的数据 - update data in one table with the values from another table 如何使用另一个表中的选定值更新表 - How to update table, with selected values from another table