Update Statement using values from multiple tables

Question

I am trying to update values in my Firebird-SQL database where I need to use values from other tables in the WHERE clause.

The statement looks like this:

UPDATE table1 SET date = 'TODAY' FROM table2
WHERE table2.id = table1.table2_id
AND table2.value1 >= table2.value2

Apparently, this doesn't work in Firebird-SQL.

It's probably a very simple question, but I'm not used to Firebird-SQL.

Answer 1

@Gordon Linoff's answer is a perfectly good approach. An alternative is to use merge:

MERGE INTO table1
USING      table2
ON         (table2.id = table1.table2_id AND table2.value1 >= table2.value2)
WHEN MATCHED THEN
   UPDATE SET date = 'TODAY';

Except in the most simple of cases, I tend to use MERGE instead of UPDATE

Answer 2

Try using a correlated subquery:

UPDATE table1
    SET DATE = 'TODAY'
    WHERE EXISTS (SELECT 1
                  FROM table2
                  WHERE table2.id = table1.table2_id AND table2.value1 >= table2.value2
                 );