Updating a table with data from another table

Question

So I have TABLE_ONE with two columns...ID and EXPIRE_DATE

and TABLE_TWO with the same two columns except the EXPIRE_DATE has a dummy value and needs to be populated from the first table.

I need to grab the most recent EXPIRE_DATE (latest date not last added) for a given ID from TABLE_ONE and populate all occurences of that ID in TABLE_TWO with that EXPIRE_DATE.

I guess it's clear that ID is not the PK in either table. It recurs.

How would I write this UPDATE of TABLE_TWO?

Thanks!

SQL update from one Table to another based on a ID match - http://stackoverflow.com/questions/224732/sql-update-from-one-table-to-another-based-on-a-id-match?rq=1 — Lexib0y, Jan 27 '14 at 17:29
You should always tell what database your are using, and an actual sample is welcome too (although in this case not really necessary). — Jan Doggen, Jan 27 '14 at 18:37

score 0 · Answer 1 · answered Jan 27 '14 at 17:33

0

Something like this:

update table2
set expire_date = expdate
from table2 join 
(select id, max(expire_date) expdate
from table1
where whatever
group by id ) temp on table2.id = temp.id
where whatever

Note that your two where clauses should be the same.

answered Jan 27 '14 at 17:33

Dan Bracuk

20,699
4
26
43

score 0 · Answer 2 · answered Jan 27 '14 at 17:37

You can do this with a correlated subquery in the set clause:

update table_two
    set expire_date = (select max(t1.expire_date) from table_one t1 where t1.id = table_two.id);

This is standard SQL syntax and should work in all (or almost all databases). Some databases allow joins in the update clause, but not all. Even among the ones that do, the syntax varies.

Updating a table with data from another table

2 Answers2