insert delete and insert

Question

Hi Expert, the below mentioned records having insert first then delete the same record and inserted the same record.

how we can maintain delete and insert records in output. Col2 to Col4 is PK

and wants to merge into this table tabletest20 (col1 char(23), col2 CHAR(20),col3 char(3),col4 CHAR(23),col5 CHAR(23),col6 DATETIME ) expected output:

('D','2','3','4','23','2024-02-14 16:34:40.483')
('I','2','3','4','23','2024-02-14 16:34:41.483')
('D','8','9','10','33',2024-02-14 17:35:39.483') 
('U','12','13','14','16',2024-02-14 18:35:41.483')

below mentioned is query. create table tabletest19 (col1 char(23), col2 CHAR(20),col3 char(3),col4 CHAR(23),col5 CHAR(23),col6 DATETIME )

insert into tabletest19  VALUES('I','2','3','4','23','2024-02-14 16:34:39.483')
insert into tabletest19  VALUES('D','2','3','4','23','2024-02-14 16:34:40.483')
insert into tabletest19  VALUES('I','2','3','4','23','2024-02-14 16:34:41.483')
insert into tabletest19  VALUES('I','8','9','10','33',2024-02-14 17:34:39.483') 
insert into tabletest19  VALUES('D','8','9','10','33',2024-02-14 17:35:39.483') 
insert into tabletest19  VALUES('U','12','13','14','15',2024-02-14 18:34:40.483') 
insert into tabletest19  VALUES('U','12','13','14','16',2024-02-14 18:35:41.483')

Please note it is not duplicate question and every question has different ask

Answer 1

Hello Shambhu Rai

It seems like you’re trying to manage a table tabletest20 with operations such as insert (‘I’), delete (‘D’), and update (‘U’). There is an easier way.

You can use a MERGE statement in SQL to perform these operations in a single query.

MERGE INTO tabletest20 AS target
USING (VALUES
  ('D', '2', '3', '4', '23', '2024-02-14 16:34:40.483'),
  ('I', '2', '3', '4', '23', '2024-02-14 16:34:41.483'),
  ('D', '8', '9', '10', '33', '2024-02-14 17:35:39.483'),
  ('U', '12', '13', '14', '16', '2024-02-14 18:35:41.483')
) AS source (col1, col2, col3, col4, col5, col6)
ON (target.col2 = source.col2 AND target.col3 = source.col3 AND target.col4 = source.col4)
WHEN MATCHED AND source.col1 = 'D' THEN
  DELETE
WHEN MATCHED AND source.col1 = 'U' THEN
  UPDATE SET col5 = source.col5, col6 = source.col6
WHEN NOT MATCHED AND source.col1 = 'I' THEN
  INSERT (col1, col2, col3, col4, col5, col6) VALUES (source.col1, source.col2, source.col3, source.col4, source.col5, source.col6);

This MERGE statement will:

• Delete rows from tabletest20 that match on col2, col3, and col4 with a source row where col1 is ‘D’.
• Update rows in tabletest20 that match on col2, col3, and col4 with a source row where col1 is ‘U’.
• Insert rows into tabletest20 from source rows where col1 is ‘I’ and there is no match on col2, col3, and col4.

Please note that the actual syntax and capabilities of the MERGE statement can vary between different SQL dialects, so you may need to adjust this example to fit your specific database system. Also, please make sure to test this on a small dataset or a backup copy of your data to ensure it behaves as expected. I hope this helps.

---

If this information provided here helps solve your issue, please tag this as answered, so it helps further community readers, who may have similar questions.