SQL update one table from another table.

Question

Here’s a simplified version of my problem Table 1 Key1, lastdate, lasttranstype

Table2 Table1key1, trandate, trantype

I want an SQL statement to update lastdate and lasttransdate on table1 for each record in that table using the matching record in Table2 with the latest date

My DB is progress.

Is this possible (I can of course write a program to do it but I'd rather do it just with an SQL statement)?

Answer 1

update table1, table2 
set table1.lastdate = table2.trandate, table1.lasttranstype = table2.trantype 
where table1.key1 = table2.table1key1

Answer 2

In T-SQL:

UPDATE Table1 
SET lastdate = trandate, lasttranstype = trantype
FROM Table1 INNER JOIN Table2 ON Table1.Key1 = Table2.Table1Key1

EDIT: I did not know that Progress was the name of a RDBMS. Would the following work for you?

UPDATE Table1
SET lastdate = 
(SELECT trandate FROM Table2 WHERE Table2.Table1Key1 = Table1.Key1),
lasttranstype = 
(SELECT trantype FROM Table2 WHERE Table2.Table1Key1 = Table1.Key1)