SQL表更新加入
这个问题在这里已经有了答案：
警告 ：在使用任何动态SQL之前，请阅读SQL注入。 在你的情况下，如果用户有权访问table2，可以通过将sql代码写入col_name来破解它。
SQL FIDDLE示例
declare @X int, @Y int, @stmt nvarchar(max), @params nvarchar(max)
select @params = '@X int, @Y int'
declare table_cursor cursor local fast_forward for
select distinct X, Y from Table2
open table_cursor
while 1 = 1
begin
fetch table_cursor into @X, @Y
if @@fetch_status <> 0 break
select @stmt = null
select @stmt =
isnull(@stmt + ', ', '') +
Col_Name + ' = ' + cast(Col_Value as nvarchar(max))
from Table2
where X = @X and Y = @Y
select @stmt = 'update Table1 set ' + @stmt + ' where A = @X and B = @Y'
exec dbo.sp_executesql
@stmt = @stmt,
@params = @params,
@X = @X,
@Y = @Y
end
close table_cursor
deallocate table_cursor
执行此操作的标准SQL方法需要相关的子查询：
update table1
set C = coalesce((select max(col_value)
from Table2 t2
where table1.A = t2.X and table1.B = t2.Y and
t2.Col_Name = 'A'
), C),
D = coalesce((select max(col_value)
from Table2 t2
where table1.A = t2.X and table1.B = t2.Y and
t2.Col_Name = 'D'
), D)
一些SQL引擎允许连接。 以下将是一个用于MySQL的方法：
update table1 join
(select X, Y, max(case when col_name = 'C' then col_value end) as C,
max(case when col_name = 'D' then col_value end) as D
from table2
group by X, Y
) t2
on t2.X = table1.A and t2.Y = table2.Y
set C = coalesce(t2.C, C),
D = coalesce(t2.D, D)
在这两种情况下， coalesce()用于在不匹配时保持当前值。 如果你想让NULL不匹配，那么只需删除coalesce() 。
编辑
在SQL Server中，更新/连接的语法略有不同：
update table1 join
set C = coalesce(t2.C, C),
D = coalesce(t2.D, D)
from table1 join
(select X, Y, max(case when col_name = 'C' then col_value end) as C,
max(case when col_name = 'D' then col_value end) as D
from table2
group by X, Y
) t2
on t2.X = table1.A and t2.Y = table2.Y;