如何使用基于连接的值更新特定行？ [重复]

Possible Duplicate:
SQL update query using joins

可能重复:使用连接的SQL更新查询

Table 1 has 2 columns:

表1有2列:

Has an ID column

有一个ID列
Has a datetime column

有一个datetime列

Table 2:

Has an ID column

有一个ID列
Has a datetime column

有一个datetime列

I need to update the datetime column in table 2 based on the JOIN between table 1 and table 2 on the ID.

我需要根据ID上表1和表2之间的JOIN更新表2中的datetime列。

Example:

If table1.id = table2.id, 
update datetime column on table2 
with the datetime column value of table1.

Does this make sense?

这有意义吗?

How would I go about doing this?

我该怎么做呢?

3 个解决方案

#1

Several options. A correlated subquery ought to work:

几个选项。相关子查询应该起作用:

UPDATE t2 SET ts = (SELECT ts FROM t1 WHERE t1.id = t2.td) WHERE ...

This is the easiest method but will be slow for large sets. The other method is this to do the join directly. This requires some slightly vendor-specific syntax:

这是最简单的方法,但对于大型集合来说会很慢。另一种方法是直接进行连接。这需要一些略微供应商特定的语法:

T-SQL:

UPDATE table1
SET col1 = t2.col1
FROM table1 t1 JOIN table2 t2 ON t1.id = t2.id

MySQL:

UPDATE table1 t1 JOIN table2 t2 ON t1.id = t2.id
SET t1.col1 = t2.col2

(Via: http://blog.ookamikun.com/2008/03/mysql-update-with-join.html)

#2

Try this

UPDATE t2
SET t2.datetime_col = t1.datetime_col
from table1 t1 
INNER JOIN table2 t2 ON t1.id = t2.id

#3

   Update t2 Set Datetime = t1.DateTime
   From Table1 t1 Join Table2 t2 On t2.id = t1.id

#1