CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
C Bury
C Bury

Reputation: 3

Updating different columns of many rows

I need to do this from Python to Snowflakes:

UPDATE 'table'
SET column1 = 'XXX'
WHERE id = 123;
                     
UPDATE 'table'
SET column2 = 'YYY'
WHERE id = 145;

...

UPDATE 'table'
SET column256 = 'YYY'
WHERE id = 654;

But I don't want to open hundreds of connections. Is it possible to structure this update in one single query and send to database only one statement?

It is always the same table but different columns to update.

Upvotes: 0

Views: 5113

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

One method uses values and conditional logic:

UPDATE table t
    SET column1 = (CASE WHEN col = 'column1' THEN v.value ELSE column1 END),
        column2 = (CASE WHEN col = 'column2' THEN v.value ELSE column2 END),
        column256 = (CASE WHEN col = 'column256' THEN v.value ELSE column256 END)        
    FROM (VALUES (123, 'XXX', 'column1'),
                 (145, 'YYY', 'column2'),
                 (654k, 'YYY', 'column256')
         ) v(id, value, col)
    WHERE t.id = v.id

Upvotes: 2

Related Questions