CODEWITHSUNDEEP
sqlsql-updatesnowflake-cloud-data-platform
user2715877
user2715877

Reputation: 543

SQL - UPDATE (Using existing rows in same table)

I have a table (see below: Current Table) in which I need to UPDATE Row# 5 because a value for ID1 already exist (Row# 4) and a value for ID3 already exist (Row# 3). There are three (3) use cases on which to UPDATE the table.

Additional context: In the final state, ID1 will have one and only one ID2, but can have many ID3. ID3 can have many ID1 and ID2. ID1 and ID3 are unique values, iff ID1 OR ID3 does not exist in table, then ID2 is assigned the value of ID1.

Use cases for UPDATE

  1. An existing row (smaller row # and TIME value) has the same ID1 value. Use the ID2 value for that existing ID1 value.
  2. An existing row (smaller row # and TIME value) has the same ID3 value. Use the ID2 value for that existing ID3 value.
  3. Both ID1 and ID3 values exist (Not the same rows though). Use the ID2 value from the row with the smallest (oldest) TIME value.

Current Table 

Row    ID1    ID2    ID3    TIME  
1      A      A      123    12:01AM  
2      A      A      456    12:05AM  
3      A      A      789    12:10AM  
4      B      A      123    12:15AM  
5      B      B      789    12:20AM  
6      C      C      987    12:25AM

Updated Table

Row    ID1    ID2    ID3    TIME  
1      A      A      123    12:01AM  
2      A      A      456    12:05AM  
3      A      A      789    12:10AM  
4      B      A      123    12:15AM  
5      B      A      789    12:20AM  
6      C      C      987    12:25AM

Upvotes: 1

Views: 3193

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270833

I think your conditions simplify to getting the earliest id2 from a matching id1 or id3:

update t
    set id2 = tt.new_id
    from (select t.*,
                 (select t2.id2
                  from t t2
                  where t2.id1 = t.id1 or t2.id3 = t.id3
                 ) as new_id
          from t
         ) tt
    where t.id1 = tt.id1;

Upvotes: 3

Related Questions