I have been trying to group INSERT and UPDATE statements together inside a “MERGE” statement with the following condition:

• Key: location_cd, machine_cd, product_cd
• If the status of the current record is 0, the status of the data input is 1, then insert a new record and update current record with [lastest_flag]column’s value from “1” to “0”.
• Otherwise, just update status of current record.

Sample Table: tableA

Input data: input1

Input data: input2

Expected output:

• with input1 we will use UPDATE statement.
• with input2 we will INSERT new record and update status of current record.

I tried this, but it is not working.

1. Define input data

   DECLARE @input_data table(
       location_cd varchar(10) NOT NULL,
       machine_cd varchar(10) NOT NULL,
       product_cd varchar(6) NOT NULL,
       status varchar(1) NOT NULL,
       updated_timestamp varchar(20) NOT NULL
   );
   
   Insert into @input_data
   VALUES ('0012345678','1234567890','123456','0','2024/06/10 15:40:15'),
       ('0012345678','1234567890','111111','1','2024/06/10 15:40:15')
   

2. Here is my SQL with MERGE statement. When I ran it, I got error:

   Error 156 Incorrect syntax near the keyword ‘BEGIN’

   MERGE INTO tableA AS Target
   Using @input_data AS Source
   ON Target.location_cd = Source.location_cd
   AND Target.machine_cd = Source.machine_cd
   AND Target.product_cd = Source.product_cd
   WHEN MATCHED AND Target.status = 1 AND Source.status = 0 AND Target.updated_timestamp < Source.updated_timestamp THEN
       UPDATE SET
            Target.status = Source.status,
            Target.updated_timestamp = Source.updated_timestamp
   WHEN MATCHED AND Target.status = 0 AND Source.status = 1 AND Target.updated_timestamp < Source.updated_timestamp THEN
       BEGIN
           INSERT (machine_cd,location_cd,product_cd,status,lastest_flag,updated_timestamp)
           VALUES (Source.machine_cd,Source.location_cd,Source.product_cd,Source.status,'1',Source.updated_timestamp);
           UPDATE SET Target.lastest_flag = '0';
       END;