Skip to content
Advertisement

Copying data from rows in the same table if it meets multiple conditions

I have a table that looks this

ID C1 C2 C3 C4 Date
101 1 1 1 1 01/01/2020
101 1 1 2 2 01/03/2020
101 1 2 3 01/05/2020
101 2 3 4 3 01/07/2020
201 1 1 1 1 02/02/2020
201 1 1 2 2 02/04/2020
201 1 2 3 02/06/2020
201 2 3 4 3 02/08/2020

What I need to do is change the Date column for ID = 201 so that it matches ID=101. Columns C1:C4 are used to identify the rows. So for example the last row with the Date = 02/08/2020 should be changed to 01/07/2020 as both their C1:C4 are the same.

Column C4 can have null values but other 3 columns will not be null. For every record in ID=101, there will be a corresponding record in ID=201 with same values for C1:C4. Every combination of C1:C4 will be unique.

I tried looking for examples with SQL UPDATE statements but could not find one that matched my case so any help would be appreciated! Thank you

UPDATE: The following code worked to deal with the Null values

set t2.date = t1.date
from t t1 join t t2
ON CONCAT(t1.c1,t1.c2,t1.c3,t1.c4) = CONCAT(t2.c1,t2.c2,t2.c3,t2.c4)
where t1.id = 101 and t2.id = 202

Advertisement

Answer

You can use update with join. One method is:

update t2
    set t2.date = t1.date
    from t t1 join
         t t2
         on t1.c1 = t2.c1 and t1.c2 = t2.c2 and t1.c3 = t2.c3 and t1.c4 = t2.c4
    where t1.id = 101 and t2.id = 202;

If c4 can be NULL,

update t2
    set t2.date = t1.date
    from t t1 join
         t t2
         on t1.c1 = t2.c1 and t1.c2 = t2.c2 and t1.c3 = t2.c3 and
            (t1.c4 = t2.c4 or t1.c4 is null and t2.c4 is null)
    where t1.id = 101 and t2.id = 202;
User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement