UPDATE statement - with two tables

[gooseriver]

I am trying to convert this to an update statement in which I want to update tb2.effdt = tb1.startdt where tb2.type = 'zenster' and tb2.effdt< tb1.startdt

select distinct *
from Table1 tb1, Table2 tb2
where tb1.id=tb2.id and tb2.type='zenster' and tb2.effdt< tb1.startdt;

I have tried multiple update statements but keep getting zero records updated.

 

[Beilstwh]

update table2 tb2
set tb2.effdt = 
(select max(tb1.effdt)
 from table1 tb1
where tb2.effdt < tb1.startdt)
where tb2.type = 'zenster';

Bill
Lead Application Developer
New York State, USA