Perform UPDATE with values from another table, in columns with the same name

Question

I have the following tables table and table, These tables have about 20 identical columns. What I need to do is update the values of the tabela_B to start from the tabela_A, where the columns are the same.

There is a way to automate the UPDATE process without having to reference the columns one by one through a JOIN?

Answer 1

Try the following:

UPDATE
    Table_B
SET
    Table_B.col1 = Table_A.col1,
    Table_B.col2 = Table_A.col2
FROM
    Table_A
INNER JOIN
    Table_B
ON
    Table_A.id = Table_B.id

Taken from: https://stackoverflow.com/questions/2334712/update-from-select-using-sql-server

Answer 2

You can make a script to generate the update and then run it dynamically.

Creating the structure of the base:

CREATE TABLE Table1
    ([Id] int, [name] varchar(50));

CREATE TABLE Table2
    ([Id] int, [name] varchar(50));

INSERT INTO Table1
    ([Id], [Name])
VALUES
    (1, 'C3PO'),
    (2, 'R2D2'),
    (3, 'BB8');

INSERT INTO Table2
    ([Id], [Name])
VALUES
    (1, 'Luke'),
    (2, 'Darth');

Creating the script that will generate UPDATE run it:

declare @query NVARCHAR(MAX)
declare @name VARCHAR(50)

set @query = 'update table2 set '

DECLARE cursor1 CURSOR FOR
select name from syscolumns
where [id] = (select [id] from sysobjects where name = 'Table1') -- Busca o objeto da tabela referência

OPEN cursor1
FETCH NEXT FROM cursor1 INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
    set @query = @query + 'Table2.' + @name + ' = Table1.' +@name + ',' 

    FETCH NEXT FROM cursor1 INTO @name
END

set @query = substring(@query, 1, (len(@query) - 1)) + ' from Table1 INNER JOIN Table2 ON Table1.id = Table2.id'

print @query; -- Verifica a query gerada

execute sp_executesql @query; -- Executa a query

CLOSE cursor1;
DEALLOCATE cursor1;

Answer 3

You cannot update multiple tables in the same query, however, you can use a transaction to make sure that the two querys of UPDATE are treated:

BEGIN TRANSACTION;

UPDATE Table1
  SET Table1.LastName = 'DR. XXXXXX' 
FROM Table1 T1, Table2 T2
WHERE T1.id = T2.id
and T1.id = '011008';

UPDATE Table2
SET Table2.WAprrs = 'start,stop'
FROM Table1 T1, Table2 T2
WHERE T1.id = T2.id
and T1.id = '011008';

COMMIT;