|Subject||RE: [firebird-support] Problems inserting a new record on not matched condition|
I am trying to insert a record into a file when I get an unmatched condition on the merge but I get an error saying it can't find the column for the insert.
Here is the code:
merge into tableA e
using tableB s
on (e.field1 = s.field2)
when not matched then insert (ACTION,ITEMID,TITLE,SITEID,CURRENCY,STARTPRICE,BUYITNOWPRICE,QUANTITY,RELATIONSHIP,RELATIONSHIPDETAILS,CUSTOMLABEL,MATCHED,VENDOR)
values ('End',e.ITEMID, e.TITLE,e.SITEID,e.CURRENCY,e.STARTPRICE,e.BUYITNOWPRICE,e.QUANTITY,e.RELATIONSHIP,e.RELATIONSHIPDETAILS,e.CUSTOMLABEL,'N','Sullivans');
What I am trying to accomplish is if there isn't a match I need to insert a record into tableA because tableA will be an input table into a subsequent process and the data I need is only on tableA.
Using Firebird 2.5
Any help/direction/ would be greatly appreciated!!
<SL> It appears that you have your table aliases backwards… you should be using “s.” not “e.” in:
<SL> Why are you using MERGE if you don’t have a WHEN MATCHED THEN clause?
<SL> It seems that you are really looking to:
INSERT INTO TableA (
FROM tableB s
NOT EXISTS (
FROM TableA e
WHERE e.field1 = s.field2