Update single column in a table with with multiple conditions in the WHERE statement

0
votes

I am trying to write an UPDATE statement to update a column in a table based on multiple WHERE conditions. See query below,

UPDATE table_new
SET col_4 = 'new value'
WHERE ?
(SELECT col_1, col_2, col_3 FROM table_new
EXCEPT
SELECT col_1, col_2, col_3 FROM table_old);

I trying to update col_4 with the new value for the unique col_1 + col_2 + col_3 combination coming from the EXPECT SQL query. I am not sure about what would follow the WHERE condition as the WHERE clause is usually followed by just one column in an UPDATE statement. I am thinking of doing a CONCAT for the unique col_1 + col_2 + col_3 combination for both the EXPECT SQL query and the column name that would follow the WHERE clause but not sure if that would help my case

My possible solution:

UPDATE table_new
SET col_4 = 'new value'
WHERE CONCAT('col_1','-','col_2','-','col_3') IN
(SELECT CONCAT('col_1','-','col_2','-','col_3') FROM table_new
EXCEPT
SELECT CONCAT('col_1','-','col_2','-','col_3') FROM table_old
);

Sample Data in table_new (Before running the UPDATE statement):

 Col_1   Col_2  Col_3  Col_4 (old value)
 123456  123XYZ 456ABC  100
 654321  ZYX321 CBA654  200

Desired Result in table_new (After running the UPDATE statement):

Col_1   Col_2  Col_3  Col_4 (new value)
 123456  123XYZ 456ABC  300
 654321  ZYX321 CBA654  400
2
sqlsql-updatesnowflake-cloud-data-platform
Please provide sample data and desired results, along with an appropriate database tag. – Gordon Linoff
I have added the sample data and desired results along with the appropriate database tag for better understanding – Jude92

2 Answers

1
votes

No need to concat variables, you can compare and group by multiple columns at the same time with something like WHERE (b.x,b.y,b.z) = (a.x,a.y,a.z):

create temp table a_new as 
select 'a' x, 'b' y, 'c' z, 100 value
union all
select 'a1', 'b1', 'c1', 200 value; 

create temp table a_old as 
select 'a' x, 'b' y, 'c' z, 300 value
union all
select 'a1', 'b1', 'c1', 500 value; 

update a_new as a
set value = b.value
from a_old b
where (b.x,b.y,b.z) = (a.x,a.y,a.z);

enter image description here

1
votes

I think you would be better off just using a join clause:

UPDATE table_new
SET col_4 = 'new value'
FROM table_new tn
LEFT JOIN table_old to ON tn.col_1 = to.col_1 
                       AND tn.col_2 = to.col_2
                       AND tn.col_3 = to.col_3
WHERE to.col_1 IS NULL;

the left join will give you matching/non matching records, and you can then determine where the records from the old table aren't in the new table by looking for the null fields in the right side (table_old) of those results. If you need it to be more specific, you could add IS NULL statements in the WHERE clause for all the column names.