I have these tables and values:
Table1
------------------------
ID | Value
------------------------
2 | asdf
4 | fdsa
5 | aaaa
Table2
------------------------
ID | Value
------------------------
2 | bbbb
4 | bbbb
5 | bbbb
I want to update all the values in Table2 using the values in Table1 with their respective ID's.
Is there a way to do that with a simple SQL query?
This question is related to
sql
sql-server
sql-server-2008
tsql
If you have ids in both tables, the following works:
update table2
set value = (select value from table1 where table1.id = table2.id)
Perhaps a better approach is a join:
update table2
set value = table1.value
from table1
where table1.id = table2.id
Note that this syntax works in SQL Server but may be different in other databases.
None of above answers worked for me in MySQL, the following query worked though:
UPDATE
Table1 t1
JOIN
Table2 t2 ON t1.ID=t2.ID
SET
t1.value =t2.value
WHERE
...
You can use alias to improve the query:
UPDATE t1
SET t1.Value = t2.Value
FROM table1 AS t1
INNER JOIN
table2 AS t2
ON t1.ID = t2.ID
I have used this one on MySQL, MS Access and SQL Server. The id fields are the fields on wich the tables coincide, not necesarily the primary index.
UPDATE DestTable INNER JOIN SourceTable ON DestTable.idField = SourceTable.idField SET DestTable.Field1 = SourceTable.Field1, DestTable.Field2 = SourceTable.Field2...
Source: Stackoverflow.com