Update multiple rows at once (postgres)

Question

I have 3 arrays. For example :

let status = [1,2,3];
let name = ['Andrey','Vasya','Petia'];
let age = [23,45,54];

Also I have array of ids for each user which I want to update .

let id_list = [2323,3434,3434]

I want to send postgres request by which I update this data in this way by one request :

UPDATE users SET status = '1' , name = 'Andrey', age = '23' WHERE id ='2323'
UPDATE users SET status = '2' , name = 'Vasya', age = '45' WHERE id ='3434'

etc .

All data I want to update in one request

Michel Milezzi · Accepted Answer · 2018-05-29 16:33:02Z

6

First of all you must unnest your array:

WITH sample (id, name, status, age) AS (
    SELECT 
        * 
    FROM 
        --Using unnest function
        unnest(
            ARRAY[2323, 3434, 3434], 
            ARRAY['Andrey','Vasya','Petia'], 
            ARRAY[1,2,3], 
            ARRAY[23,45,54]
        )
)
--And then proceed to your update
UPDATE 
    users 
    SET status = s.status , name = s.name, age = s.age 
    FROM sample s
    WHERE users.id = s.id;

More info about unnest function here.

edited May 29, 2018 at 16:33

answered May 25, 2018 at 11:47

Michel Milezzi

11.4k3 gold badges24 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Andrey Radkevich Over a year ago

is it all one request ?

Collectives™ on Stack Overflow

Update multiple rows at once (postgres)

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related