CODEWITHSUNDEEP
sqlhsqldbsql-merge
Gyro Gearloose
Gyro Gearloose

Reputation: 1144

Understanding MERGE INTO with hsqldb

I have an empty table and would like to insert some row if it is not already there. I tried 3 (major) variants of statements. The first works, the second gives no error but doesn't insert anything, the third even throws an error.

I would like to understand why the second statement does not do anything.

First is (does insert):

MERGE INTO tags ta USING
 (VALUES 91852, 'G') temp (fid, tag) 
 ON temp.fid = ta.fid
 WHEN NOT MATCHED THEN
 INSERT (fid, tag) VALUES (temp.fid, temp.tag);

Second is (does no insert):

MERGE INTO tags ta USING
 (SELECT fid, tag FROM tags i WHERE i.fid=91852 AND i.tag='G') temp (fid, tag) 
 ON temp.fid = ta.fid
 WHEN NOT MATCHED THEN
 INSERT (fid, tag) VALUES (91852, 'G');

If the SELECT returns nothing, then the NOT MATCHED statement should fire, right?

Third is (with error):

sql> MERGE INTO tags ta USING
 (SELECT fid, tag FROM tags i WHERE i.fid=91852 AND i.tag='G') temp (fid, tag) 
 ON temp.fid = ta.fid
 WHEN NOT MATCHED BY temp THEN
 INSERT (fid, tag) VALUES (91852, 'G');

  +>   +>   +>   +> SEVERE  SQL Error at '<stdin>' line 183:
"MERGE INTO tags ta USING
 (SELECT fid, tag FROM tags i WHERE i.fid=91852 AND i.tag='G') temp (fid, tag) 
 ON temp.fid = ta.fid
 WHEN NOT MATCHED BY temp THEN
 INSERT (fid, tag) VALUES (91852, 'G')"
unexpected token: BY required: THEN : line: 4
sql> sql>

The last version was inspired by https://stackoverflow.com/a/30938729/4142984 , which is for sql-server, not for hsqldb.

Upvotes: 0

Views: 2033

Answers (1)

Matze
Matze

Reputation: 325

The SELECT part of a merge statement is your source set. If it is empty, then nothing will be inserted, because there is nothing to match. You have just an empty result set.

In general MERGE takes the result set acquired by your USING clause and joins it with your ON condition. Then you can tell what to do for both possibilities. If it matches the condition you may update the record, if not you may insert one. The data to update or insert comes from the USING clause, so if your USING result set is empty you have nothing to match and nothing to insert.

Upvotes: 2

Related Questions