patternsqlMinor

Combine the effects of separate delete and insert rows

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

rowstheinsertcombinedeleteeffectsseparateand

Problem

How to I combine the two rows below:

Date          Id   For_Id   Type         New_Value    Old_Value
------------------------------------------------------------------
2015-08-13    6    327      DELETE       NULL         Value1
2015-08-13    6    327      INSERT       Value2       NULL

...into a single row, removing the NULLs, matching rows based on the dates?

Date          Id   For_Id   New_Value    Old_Value 
------------------------------------------------- 
2015-08-13    6    327      Value2       Value1

Solution

If I'm understanding your needs correctly, this would probably work...

SELECT t1.Date, t1.Id, t1.For_Id, t1.New_Value, t2.Old_Value
FROM table t1 JOIN table t2
ON t1.Date = t2.Date
AND t1.Id = t2.Id
AND t1.For_Id = t2.For_Id
WHERE t1.New_Value IS NOT NULL
AND t2.Old_Value IS NOT NULL;

Code Snippets

SELECT t1.Date, t1.Id, t1.For_Id, t1.New_Value, t2.Old_Value
FROM table t1 JOIN table t2
ON t1.Date = t2.Date
AND t1.Id = t2.Id
AND t1.For_Id = t2.For_Id
WHERE t1.New_Value IS NOT NULL
AND t2.Old_Value IS NOT NULL;

Context

StackExchange Database Administrators Q#110968, answer score: 2

Revisions (0)

No revisions yet.