SQL merge command on one table

Question

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.

Answer 1

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!

Answer 2

MERGE INTO accounting_values AS Target
USING (select @entity_id as entity_id, @fiscal_year as fiscal_year, @fiscal_period as       fiscal_period) AS source
ON (target.entity_id = source.entity_id  AND target.fiscal_year = source.fiscal_year AND target.fiscal_period = source.fiscal_period)
WHEN MATCHED THEN
    UPDATE SET value = @value
WHEN NOT MATCHED BY Target THEN
    INSERT 
(entity_id, fiscal_year, fiscal_period, value) 
    VALUES (@entity_ID, @fiscal_year, @fiscal_period, @value);

This is tested. It doesn't reference the table twice. I was trying to duplicate MySQL's ON DUPLICATE KEY UPDATE.