Search code examples
sqlmergefirebird

Why my SQL MERGE inside an EXECUTE BLOCK with doesn't work?

A table1 contains a list of ids. The goal is to check if inside a table3 (for each id from the table1) there is a row which contains this id. If not, I would like to insert a new row which contains a list of values.

I've tried to do it with this block:

SET TERM #;
EXECUTE block AS
DECLARE idA bigint;
DECLARE idB bigint;
BEGIN
FOR SELECT id_x, id_y FROM table1 inner join table2 on table1.column = 'random' into :idA, :idB
do
MERGE INTO table3 FCA USING (SELECT '1' AS TEST FROM RDB$DATABASE) R ON (table3.column1= :idA)
WHEN NOT MATCHED THEN INSERT (column1, column2, column3) VALUES (null, :idB, :idA)
END#
SET TERM;#

However, it doesn't work (the MERGE statement). Actually, I've tried with an INSERT INTO. It works, but as you can see, it's not what I want.

So, why the merge doesn't work?

Solution

  • MERGE is working as RIGHT JOIN. In your case join condition is always fulfilled so NOT MATCHED action is never performed.