Update multiple rows using select statement

Question

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Run a select to make sure it is what you want  SELECT t1 value AS NEWVALUEFROMTABLE1 t2 value AS OLDVALUETABLE2   FROM Table2 t2 INNER JOIN Table1 t1 on t1 ID   t2 ID   Update  UPDATE Table2 SET Value   t1 Value FROM Table2 t2 INNER JOIN Table1 t1 on t1 ID   t2 ID   Also  consider using BEGIN TRAN so you can roll it back if needed  but make sure you COMMIT it when you are satisfied

[sql] Update multiple rows using select statement

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2008

Examples related to tsql