[SOLVED] MERGE with UPSERT, not inserting any value in table

MERGE with UPSERT, not inserting any value in table

Table structure:

CREATE TABLE [dbo].[LockOfferByOfferUses](
[OfferID] [int] NULL,
[OfferCode] [varchar](50) NULL,
[LockCounter] [int] NULL,
[UpdatedDate] [datetime] NULL
) ON [PRIMARY]

SQL statement:

merge into LockOfferByOfferUses as Target
using (Select * from LockOfferByOfferUses Where OfferID=123 And OfferCode='abc' ) as Source
on Target.OfferID = Source.OfferID
when NOT MATCHED then
INSERT (OfferID,OfferCode,LockCounter,UpdatedDate) VALUES (123,'abc',1,GETDATE())
when MATCHED then 
update set Target.LockCounter=Target.LockCounter+1;

What I am trying to do is, check if a record exists in "LockOfferByOfferUse" by the offerID. If it doesn't exist, insert a record; If exists update "LockCounter" column.

The update part works, but the insertion isn't working. It's displaying message "(0 row(s) affected)". No error message.

Solution

The source should be the data you want to use - not another query against the target table.

So it should be something like:

merge into LockOfferByOfferUses as Target
using (VALUES(123)) as Source (OfferID)
on Target.OfferID = Source.OfferID
when NOT MATCHED then
INSERT (OfferID,OfferCode,LockCounter,UpdatedDate) VALUES (Source.OfferID,'abc',1,GETDATE())
when MATCHED then 
update set Target.LockCounter=Target.LockCounter+1;

(It's up to you where you move the other values up into the Source also, or just have them as literals in the INSERT - for more complex queries, more of the values might also be required in multiple places).