Update records in one table using another table

问题:

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.

回答1:

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

回答2:

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.

回答3:

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)

回答4:

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