Update query Oracle

I have tab开发者_开发百科le 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