SQL merge command on one table

merge, sql, sql-server-2008

Solution

As discussed here, you'll see that a merge is exactly as it sounds. taking two tables and searching for the value you joined them on lets call it "X". if X is a match then you perform an update on that record. If it does not exist then you would perform an insert on the targeted table using the values selected.

In your case i'm not entirely sure if your join

  (  ON (SOURCE.colID = 1)   )

is correct. i'm pretty sure this needs to be

on(Source.colID = Target.colID)

So the full statement should be this:

MERGE INTO CSScolorOrders AS TARGET 
USING (SELECT * FROM CSScolorOrders WHERE ordID = 3) AS SOURCE 
on(Source.colID = Target.colID)
WHEN MATCHED THEN 
  UPDATE SET corQuantity = 1 
WHEN NOT MATCHED BY TARGET
  THEN INSERT (colID, ordID, corQuantity) VALUES (1, 3, 1);

but i haven't tested this and am not 100% sure what your table columns are and what exactly you're attempting to join. But the link i provided should point you in the right direction.

Hope this helps!

Problem

I am trying to get my head over `MERGE` sql statement. What I want to achieve is: Insert new values into the `CSScolorOrders` table but update `corQuantity` column if the record with `colID` and `ordID` already exist This is what I ended up with: ``` MERGE INTO CSScolorOrders AS TARGET USING (SELECT * FROM CSScolorOrders WHERE ordID = 3) AS SOURCE ON (SOURCE.colID = 1) WHEN MATCHED THEN UPDATE SET corQuantity = 1 WHEN NOT MATCHED BY TARGET THEN INSERT (colID, ordID, corQuantity) VALUES (1, 3, 1); ``` Unfortunately it does not raise any exception so I do not know why it doesn't work.

Original source