0

I am attempting to execute the below syntax and I get two errors

A MERGE statement must be terminated by a semi-colon (;).
Incorrect syntax near '='.

Now if I comment out the last line of code which is an update statement the syntax executes perfectly. Is there a way to run both an Insert() and Update() statement in one Merge()

Here is syntax

MERGE INTO EmpSales es
USING (
    SELECT employee, COUNT(sales) as SaleCount
    FROM salesInfo
    WHERE [Sale Date] BETWEEN '01/01/2016' AND '12/31/2016'
    GROUP BY employee
) cs
    ON es.employee = cs.employee
WHEN MATCHED THEN
    UPDATE SET es.TotalSales = cs.SaleCount
WHEN NOT MATCHED BY TARGET THEN
  INSERT (employee, TotalSales)
  VALUES (cs.employee, cs.SaleCount)
  UPDATE SET es.Madrigal = '0';
  • 1
    Your WHEN NOT MATCHED clause cannot contain an UPDATE clause. You need a semi-column after the VALUES clause; – Dan Guzman Apr 29 '17 at 19:59
  • Can I run both an update and an insert in the WHEN NOT MATCHED? That is what I am wanting to do – IcyPopTarts Apr 29 '17 at 20:26
  • 6
    If you want to set es.Madrigal to "0" for all records, it needs to be a separate statement. If you're trying to update the record(s) you just inserted - why not just INSERT (employee, totalSales, Madrigal) VALUES (cs.employee, cs.SaleCount, '0')? – RDFozz Apr 29 '17 at 20:49
1

It's not completely clear what you're trying to accomplish. However, it seems most likely that you're trying to update EmpSales.Madrigal to '0' after your insert.

To set EmpSales.Madrigal to '0' for all newly inserted records:

MERGE INTO EmpSales es
USING (
    SELECT employee, COUNT(sales) as SaleCount
    FROM salesInfo
    WHERE [Sale Date] BETWEEN '01/01/2016' AND '12/31/2016'
    GROUP BY employee
) cs
    ON es.employee = cs.employee
WHEN MATCHED THEN
    UPDATE SET es.TotalSales = cs.SaleCount
WHEN NOT MATCHED BY TARGET THEN
  INSERT (employee, TotalSales, Madrigal)
  VALUES (cs.employee, cs.SaleCount, '0')
;

To set EmpSales.Madrigal to '0' for all inserted or updated records:

MERGE INTO EmpSales es
USING (
    SELECT employee, COUNT(sales) as SaleCount
    FROM salesInfo
    WHERE [Sale Date] BETWEEN '01/01/2016' AND '12/31/2016'
    GROUP BY employee
) cs
    ON es.employee = cs.employee
WHEN MATCHED THEN
    UPDATE SET es.TotalSales = cs.SaleCount, es.Madrigal = '0'
WHEN NOT MATCHED BY TARGET THEN
  INSERT (employee, TotalSales, Madrigal)
  VALUES (cs.employee, cs.SaleCount, '0')
;

If you're looking for something else, please clarify what you need.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.