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;