Interbase SQL Multifield Update

Question

The Database is Interbase (gdb). I have two tables "RESTS" and "FORMS". Each have several fields but some are identical but may have different values or may have no value at all. The fields that are similar are "FULLNAME", "PRODUCER" and "ADDRESS". And the two tables are linked by "ALCCODE".

So what I ask is: Can an Update have multiple fields so that each field is updated individually?

Example that I have:

Update Rests r 

set r.FULLNAME=(Select f.FULLNAME from FORMS f where (f.FULLNAME is
not null and f.FULLNAME<>'') and f.FULLNAME<>r.FULLNAME and
r.ALCCODE=f.ALCCODE),

r.PRODUCER=(Select f.PRODUCER from FORMS f where (f.PRODUCER is not
null and f.PRODUCER<>'') and f.PRODUCER<>r.PRODUCER and
r.ALCCODE=f.ALCCODE),

r.ADDRESS=(Select f.ADDRESS from FORMS f where (f.ADDRESS is not null
and f.ADDRESS<>'') and f.ADDRESS<>r.ADDRESS and r.ALCCODE=f.ALCCODE)

where ((r.FULLNAME is null or r.FULLNAME='') and Exists(Select
f.FULLNAME from FORMS f where (f.FULLNAME is not null and
f.FULLNAME<>'') and f.FULLNAME<>r.FULLNAME and r.ALCCODE=f.ALCCODE)) 

and ((r.PRODUCER is null or r.PRODUCER='') and Exists(Select
f.PRODUCER from FORMS f where (f.PRODUCER is not null and
f.PRODUCER<>'') and f.PRODUCER<>r.PRODUCER and r.ALCCODE=f.ALCCODE)) 

and ((r.ADDRESSis null or r.ADDRESS='') and Exists(Select f.ADDRESS
from FORMS f where (f.ADDRESS is not null and f.ADDRESS<>'') and
f.ADDRESS<>r.ADDRESS and r.ALCCODE=f.ALCCODE))

But it doesn't work the way I want. Actually it doesn't work at all!

Yet, for one field it does:

Update Rests r set r.FULLNAME=(Select f.FULLNAME from FORMS f where
(f.FULLNAME is not null and f.FULLNAME<>'') and f.FULLNAME<>r.FULLNAME
and r.ALCCODE=f.ALCCODE) where ((r.FULLNAME is null or r.FULLNAME='')
and Exists(Select f.FULLNAME from FORMS f where (f.FULLNAME is not
null and f.FULLNAME<>'') and f.FULLNAME<>r.FULLNAME and
r.ALCCODE=f.ALCCODE))

How can I make the same for two or more fields in one update?

Answer 1

I think you can improve this code a lot, but as far as it's "not working", I believe your issue is rooted in your AND logic. What you have in your question basically says UPDATE 3 things WHEN condition A AND condition B AND condition C, meaning this query will not do anything unless ALL THREE parts of your WHERE clause are true. If I'm understanding your problem correctly, what your actually intend here is to update each column independently of the rest, so for example if r.FULLNAME is missing but f.FULLNAME is present, you want to update this field regardless of whether r.PRODUCER and r.ADDRESS meet the criteria.

In terms of fixing your query, if I've understood your problem correctly, I think the following will do what you want:

UPDATE RESTS r
INNER JOIN FORMS f
ON r.ALCCODE = f.ALCCODE
SET r.FULLNAME=coalesce(NULLIF(r.FULLNAME, ''), NULLIF(f.FULLNAME,''))
, r.PRODUCER=coalesce(NULLIF(r.PRODUCER,''), NULLIF(f.PRODUCER,''))
, r.ADDRESS=coalesce(NULLIF(r.ADDRESS,''), NULLIF(f.ADDRESS,''));

The first three lines I think are self explanatory, you're joining the two tables based on the common ID. This removes the repeated r.id = f.id checks in your query. In terms of the rest:

• NULLIF(r.FULLNAME, '') returns NULL if r.FULLNAME = '', otherwise it returns the value of r.FULLNAME.
• coalesce(NULLIF(r.ADDRESS,''), NULLIF(f.ADDRESS,'')) - returns the first non-NULL value in the list.
• SET r.FULLNAME=coalesce(NULLIF(r.FULLNAME, ''), NULLIF(f.FULLNAME,'')) will SET r.FULLNAME = to the first value which does not equal NULL or ''.

I believe this satisfied all the criteria you were trying to express if your WHERE clauses, but do comment if you think I've missed something.

Here is a fiddle of the above doing it's thing: link

Answer 2

Try This one

UPDATE Rests SET 
    FULLNAME= CASE WHEN F.FULLNAME IS null or F.FULLNAME = '' or R.FULLNAME=F.FULLNAME THEN R.FULLNAME ELSE F.FULLNAME END,
    ADDRESS= CASE WHEN F.ADDRESS IS null or F.ADDRESS = '' or R.ADDRESS=F.ADDRESS THEN R.ADDRESS ELSE F.ADDRESS END,
    PRODUCER=CASE WHEN F.PRODUCER IS null or F.PRODUCER = '' or R.PRODUCER=F.PRODUCER THEN R.PRODUCER ELSE F.PRODUCER END
    FROM Rests R,FORMS F
    WHERE R.ALCCODE=F.ALCCODE