CODEWITHSUNDEEP
sqlpostgresqlsql-update
Flask Diploy
Flask Diploy

Reputation: 29

Updating column of multiple rows with multiple value based on primary keys in postgres

I have a table named test, of multiple rows,in that there are three columns: id, data, content

I have a list of ids and respective data for that key. Something like this:

list = [(1,"a"),(2,"b"),(10,"c")]

Now I want to update only those rows which has ids as 1, 2, 10 with values "a", "b" and "c" in data column respectively. That is I want to update column data with values "a", "b" and "c" for ids 1,2 and 10.

I have tried different approaches but they are failing.

I don't want to use "case when" in update logic as there will be lots of multiple rows (here for the shake of example i have taken only 3 rows to update for data but in real it would be more than that.)

Upvotes: 0

Views: 606

Answers (1)

user11350468
user11350468

Reputation: 1407

Try the below with update from:

update test_table set data = temp_table.data
from (values
    (1, 'a'),
    (2, 'b'),
    (10, 'c')
) as temp_table(id, data) 
where temp_table.id = test_table.id;

Demo - SQL Fiddle

Upvotes: 1

Related Questions