Firebird UPDATE a field using conditions of other table
I have two tables in Firebird with the following fields:
TABLEA: KEY, ITEM, LAST_SALE_DATE TABLEB: KEY, ROT
Both are related by the key. They refer to the same item using the key field.
I want to update TABLEB.ROT='A' where TABLEA.LAST_SALE_DATE>01/01/2016.
I can't figure how to make the statement, please help.
There are several ways you can do this. First of all you can correlate the update of TABLEB by doing an existence check on TABLEA:
update TABLEB b set b.ROT = 'A' where exists ( select 1 from TABLEA a where a.KEY = b.KEY and a.LAST_SALE_DATE > DATE '2016-01-01')
Another way would be to use MERGE:
merge into TABLEB b using TABLEA a on a.KEY = b.KEY and a.LAST_SALE_DATE > DATE '2016-01-01' when matched then update set b.ROT = 'A'