CODEWITHSUNDEEP
sqlfirebirdfirebird-embedded
Luca Barthmann
Luca Barthmann

Reputation: 43

Update Statement using values from multiple tables

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.

Upvotes: 2

Views: 1610

Answers (2)

Allan
Allan

Reputation: 17429

@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

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269953

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
                 );

Upvotes: 2

Related Questions