How to overwrite table-rows with data from other table

Question

I would like to overwrite rows in a table1, with other ones in table2, same database.

For example, table1 has following data:

COL1 | Col2  | Col3  | Col4  | Col5  | idDie
----------------------------------------------
0001 | unknown | unknown | unknown | unknown | frKey1
0002 | unknown | unknown | unknown | unknown | frKey2
0003 | unknown | unknown | unknown | unknown | frKey3
0004 | unknown | unknown | unknown | unknown | frKey4
0005 | unknown | unknown | unknown | unknown | frKey5
0006 | unknown | unknown | unknown | unknown | frKey6

and table2:

COL1 | Col4  | Col5 | Col6 | Col7
---------------------------------
0003 | value | value | value | value
0004 | value | value | value | value
0005 | value | value | value | value

So I want to overwrite the default data in table1, with data from table 2 (table2 has same column-names but not all columns as table1! just a couple of columns) in the range where COL1 > 0002 and COL1 < 0006

And there is another table3, which has the following form:

idDie | Col22 | Col33 | Col44

The Dies with the id's frKey3, frKey3 and frKey4 should be overwritten as well. They have a reference, foreign key, in table1, and Col6 and Col7 information in table2 are informations that should be copied to table3.

Is it possible to do that in just one sql-statement?

score 3 · Accepted Answer · answered Aug 25 '15 at 14:32

3

For SQL Server the update query you want is:

update t1
set
  t1.col4 = t2.col4,
  t1.col5 = t2.col5
from table1 t1
join table2 t2 on t1.col1 = t2.col1;

Given your sample data Table1 would look like this after the update:

COL1        Col2    Col3    Col4    Col5
----------- ------- ------- ------- -------
1           unknown unknown unknown unknown
2           unknown unknown unknown unknown
3           unknown unknown value   value
4           unknown unknown value   value
5           unknown unknown value   value
6           unknown unknown unknown unknown

answered Aug 25 '15 at 14:32

jpw

44,361
6
66
86

I am getting this error for each column on the set-statement, although both tables and all colums exist in the database --> "The multi-part identifier "dbo.xxx$.col4" could not be bound." – ZelelB Aug 25 '15 at 14:46
@ZelelB Did you use aliases for the tables like I did? The prefixes you use for the columns must match the aliases used for the tables (if any). – jpw Aug 25 '15 at 14:47
I edited the question.. Do you have a solution for this? Is it possible to do all that in just one sql-statement? – ZelelB Aug 26 '15 at 06:16
any ideas on how I can reach that? (after the edit with table3) – ZelelB Aug 26 '15 at 10:03
@ZelelB It's not possible to specify multiple tables as targets in the same update statement. You have to use multiple statements. See this question for more information: http://stackoverflow.com/questions/2044467/how-to-update-two-tables-in-one-statement-in-sql-server-2005 – jpw Aug 26 '15 at 11:01
@ZelelB On a side note: making changes to a question that has been answered isn't a good thing to do as it invalidates the answers. You should revert your last edits and instead ask a new question that addresses the new problem. – jpw Aug 26 '15 at 11:04

How to overwrite table-rows with data from other table

1 Answers1