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:
'End',e.ITEMID,e.TITLE…
<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 (
ACTION,ITEMID,TITLE,SITEID,CURRENCY,STARTPRICE,BUYITNOWPRICE,QUANTITY,RELATIONSHIP,RELATIONSHIPDETAILS,CUSTOMLABEL,MATCHED,VENDOR
)
SELECT
'End',s.ITEMID,
s.TITLE,s.SITEID,s.CURRENCY,s.STARTPRICE,s.BUYITNOWPRICE,s.QUANTITY,s.RELATIONSHIP,s.RELATIONSHIPDETAILS,s.CUSTOMLABEL,'N','Sullivans'
FROM tableB s
WHERE
NOT EXISTS (
SELECT 1
FROM TableA e
WHERE e.field1 = s.field2
)
