I have the following table and B tables, these tables have about 20 identical columns. What I need to do is to update the values of table_B to partidar of table_A, where the columns are the same.
Is there a way to automate the UPDATE process without having to reference the columns one by one through a JOIN?
You can make a script to generate the update and then do the dynamic execution.
Creating the base structure:
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 the UPDATE execute the same:
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;
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
Retrieved from: link
You can not update multiple tables in the same query, however, you can use transaction to make sure that the two querys of UPDATE are handled:
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;