Combine the effects of separate delete and insert rows

Question

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

Answer 1

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;

Answer 2

If there are multiple delete and inserts in the same day, then you would either need to sort by date and time or some other sort mechanism to determine the latest value in the table. Based on what you have posted, the same day edits won't have differentiating factors.

If your intention is to combine the rows in a select output, you will need a self inner join where you join on the id and for_id columns and pick the new_value from one table and the old value from the other table.

If your intention is to update the new_value with the updated value without the NULL, you will need an update query.