Update records in one table using another table's records as WHERE parameters

Question

I have 2 tables: Table1 and Table2. Both tables have a column named Column2.

I want to set all values of Table1.Column1 as NULL for all records that do not exist in Table2. I.e. all records where Table1.Column2 <> Table2.Column2.

This is the query I am trying to execute:

UPDATE a
SET a.Column1 = null
FROM Table1 a
INNER JOIN Table2 b
ON a.Column2 <> b.Column2

I get a "Token Unknown" Dynamic SQL error on "FROM" when I try to execute this query.

Any idea what I am doing wrong? I am fairly new to SQL so there is a good chance that I am using the wrong type of join.

Althought in this case (set null when not matching) is not possible in firebird database, in similar cases one could use [MERGE](http://www.firebirdsql.org/refdocs/langrefupd25-merge.html) statement — ghord, Jul 12 '16 at 08:38

score 3 · Answer 1 · answered Nov 01 '14 at 08:27

Firebird doesn't support joins in UPDATE-statements, instead you will need to do something like this:

UPDATE Table1
   SET Column1 = NULL
WHERE NOT EXISTS (
   SELECT 1 
   FROM Table2 
   WHERE Table2.Column2 = Table1.Column2
)

However, this seems to point to a missing foreign key constraint (from Table1 to Table2), which would have enforced this (or would have disallowed the records in Table2 being deleted if they were used in Table1).

score 1 · Answer 2 · edited Dec 19 '17 at 12:01

Your need is not very clear since you did not mention how Table1 and Table2 are linked other than Column2, so I´ll assume that such column is the joining one. You need to involve the table been modified with an alias in the query, somehow like a "cursor":

UPDATE Table1 ***Table1_0***   
   SET Column1 = NULL  
WHERE
 (select Column2 from Table2 where (Column2 = ***Table1_0***.Column2) is null

if a match is found, then the condition will return a value and the "is null" part will be false and the row will note be updated.

score 1 · Answer 3 · edited Dec 19 '17 at 12:01

1

This definitely works on Firebird 2.5:

update CREATOR c
set ALPHAINDEXID = 
(select ai.alphaindexid
from ALPHAINDEX ai
where ai.alphaindexletter = substring(c.creatorname from 1 for 1)
and ai.roleid = 5)

edited Dec 19 '17 at 12:01

Mark Rotteveel

100,966
191
140
197

answered Oct 03 '17 at 08:53

Lyndon

573
6
24

score -1 · Answer 4 · answered Oct 30 '14 at 14:38

-1

This should work in Firebird.

UPDATE a
SET a.Column1 = null
FROM Table1 a
LEFT OUTER JOIN Table2 b ON a.Column2 = b.Column2
WHERE b.Column2 IS NULL

answered Oct 30 '14 at 14:38

LeeG

708
5
14

2

It doesn't, Firebird doesn't support joins in updates. – Mark Rotteveel Nov 01 '14 at 08:21

Update records in one table using another table's records as WHERE parameters

4 Answers4

Linked