SQL Table Update with join

Question

I have two tables, Table1 and Table2, which have N no. of columns. I need to update Table1 based on Table2. I want to update all the columns in Table1 which are listed in a single column in Table2.

E.G

    Table1   A B C D E . . .
             1 2 3 4 5 . . .
             7 6 5 4 3 . . .

    Table2   X Y Col_Nam Col_Value
             1 2    C       8
             1 2    D       9
             7 6    E       10
             7 6    C       20
             . .    .       .
             . .    .       .

Update all the columns in Table1 which are listed in Table2 when matching the following condition Table1.A = Table2.X and Table1.B = Table2.Y

The Platform is SQL Server. What I am looking for is a solution which is dynamic, because I don't know the column names which I am going to update. Table1 can have N no. of columns which need to be updated. I tried the following using Cursor.

DECLARE @s Varchar(MAX), @key1 VARCHAR(MAX), @key2 VARCHAR(MAX), @Cname VARCHAR(MAX), @CValue VARCHAR(MAX)

DECLARE crs CURSOR FOR SELECT * FROM Table2
OPEN crs;
FETCH NEXT FROM crs inTO @key1,@key2,@Cname,@Cvalue;
WHILE @@FETCH_STATUS = 0
BEGIN
set @s =
                'Update T1 SET ' +  @FieldName + ' = ''' + @FieldValue +
        ''' from Table1 T1' +
        ' where T1.A = '''  + @key1 +
        ''' and T1.B = ''' + @key2 

exec(@s)


    FETCH NEXT FROM crs inTO @key1,@key2,@Cname,@Cvalue;

END

CLOSE crs
DEALLOCATE crs

Somehow it is not working and I want to put a default value for all those records which are not matching the where condition.

Any other solution or help will be appreciated.

There is a standard way to do it but a particular platform (SQL Server, Oracle, DB2, mySql etc) have "shortcuts" that are faster. What platform? — Hogan, Jul 27 '13 at 16:04
On a related note: http://english.stackexchange.com/questions/68169/is-updation-a-correct-word — James A Mohler, Jul 27 '13 at 16:05
This problem has been discussed a lot of times, please before posting this, you should have done a better search. Also you should specify the platform, it's important. — Gaston Flores, Jul 27 '13 at 17:19
@Hogan, that is correct, but the user typically does not seek an answer for all platforms, in this case I'm sure he's just looking for a single platform. The questions should be more specific and should not lead to ambiguities. — Gaston Flores, Jul 27 '13 at 18:25

Roman Pekar · Answer 1 · 2013-07-28T19:34:38.623

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

Gordon Linoff · Answer 2 · 2013-07-28T18:55:33.737

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;

Hi Gordon , Thank you for your response , I have updated my question with more details, any help will be appreciated. — user2625874, Jul 28 '13 at 18:16

SQL Table Update with join

2 Answers2