SQL Table Update with join

This question already has an answer here:

How can I do an UPDATE statement with JOIN in SQL? 14 answers

---

warning : before using any dynamic SQL, read about SQL Injection. In your case, if user have access to table2, it could be hacked by writing sql code into col_name.

SQL FIDDLE EXAMPLE

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

---

The standard SQL way to do this requires correlated subqueries:

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)

Some SQL engines allow joins. The following would be a method to use with 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)

In both cases the coalesce() is intended to keep the current value, when there is no match. If you want NULL with no match, then just remove the coalesce() .

EDIT

In SQL Server, the syntax for an update/join is slightly different:

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;

链接地址: http://www.djcxy.com/p/94662.html

上一篇: 如何使用表B中的信息更新表A中的列

下一篇: SQL表更新加入