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

  )