0

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.

Community
  • 1
  • 1
Gyro Gearloose
  • 1,056
  • 1
  • 9
  • 26

1 Answers1

2

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.

Matze
  • 325
  • 1
  • 12
  • I'm not sure I understand this the right way. Why would, see here http://stackoverflow.com/a/30938729/4142984 that "select null as test1" work? Because there is a row (albeit null) instead of my example, where there are no rows at all? – Gyro Gearloose Feb 04 '16 at 14:24
  • Hmm, might be it. Seems to me like an edge case. I do not really know about hsqldb, I have to admit. But yeah, there is still a difference between SELECT NULL ... and an empty result set. – Matze Feb 04 '16 at 14:34
  • The answer is correct. No action is taken if the source row set does not contain any rows. – fredt Feb 04 '16 at 18:15