How to write the SQL in the following scenario

Question

There are two table, also two column are the same between them

table1:
id, date

table2:
id, date

The problem is , how to update the id of table 2 that can match the date in 2 table , that means, e.g.

update table2 set table2.id = table1.id Where table1.date = table2.date

thanks

Prahalad Gaggar · Accepted Answer · 2013-06-26T10:58:40.200

3

update t2 set t2.id = t1.id 
from table2 t2
inner join table1 t1 on t1.date = t2.date

Edit

update t2 set t2.id = t1.id 
from table2 t2
inner join table1 t1 on t1.date = t2.date
where convert(date,t2.date)>'2013/01/01' --YYYY/MM/DD if your Date column is datetime.

edited Jun 26 '13 at 10:58

answered Jun 26 '13 at 10:20

Prahalad Gaggar

11,389
16
53
71

sorry but set t2.id = t2.id ? – user782104 Jun 26 '13 at 10:23
1

@user782104 by mistake **`:)`** – Prahalad Gaggar Jun 26 '13 at 10:23
how about adding a condition where t2.date > xxx? – user782104 Jun 26 '13 at 10:45
@user782104 please look at the updated answer. – Prahalad Gaggar Jun 26 '13 at 10:58

How to write the SQL in the following scenario

1 Answers1