how do I update specific rows with values based on a join?

Question

Possible Duplicate:
SQL update query using joins

Table 1 has 2 columns:

• Has an ID column

• Has a datetime column

Table 2:

• Has an ID column

• Has a datetime column

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

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?

Answer 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)

Answer 2

Try this

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

Answer 3

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