Update multiple rows using select statement

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?

---

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.

---

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.

---

您可以使用别名来改进查询：

UPDATE t1
   SET t1.Value = t2.Value
  FROM table1 AS t1
         INNER JOIN 
       table2 AS t2
         ON t1.ID = t2.ID

链接地址: http://www.djcxy.com/p/16882.html

上一篇: 在sql语句中insert语句返回多行

下一篇: 使用select语句更新多行