I have table TB1 which has the following cols:
ID | date
---------------------
1 | 12-JUL-10
2 | 12-JUL-10
3 | 12-JUL-10
4 | 12-JUL-10
.
.
.
10000 | 12-JUL-10
table2
ID | date
---------------------
1 | 12-JAN-09
2 | 12-JUL-09
3 | 12-JUL-09
4 | 12-JUL-08
.
.
.
5800 | 12-JUL-08
How to update the table2's date which has similar ID as table1.
Thanks :)
In general
UPDATE table2 t2
SET date_col = (SELECT t1.date_col
FROM table1 t1
WHERE t1.id = t2.id)
WHERE EXISTS (
SELECT 1
FROM table1 t1
WHERE t1.id = t2.id )
If you can be guaranteed that every ID in table2 exists in table1 (or if you want the date_col set to NULL if there is no match), you can eliminate the WHERE EXISTS. But generally you only want to do an update if there is a matching record.
Then there is also using an inline view for the update. This is slightly trickier to get right because I think it requires a primary key to exist on both sides of the join otherwise it fails with an error.
update (
select
t1.id as t1_id,
t1.value as t1_date,
t2.id as t2_id,
t2.value as t2_date
from
table1 t1
join table2 t2 on (t1.id = t2.id)
)
set t2_date = t1_date
