CODEWITHSUNDEEP
postgresql
Emranul Islam
Emranul Islam

Reputation: 1

How to update many rows with different values?

Thaks @Tim Biegeleisen but ,at the end of line is not needed. In this code first part(email) works fine but second part(mobileno) is not working. I have tried it with both , after end and without ,.

UPDATE table_name
    SET
        email = CASE id WHEN 1 THEN email1
                        WHEN 2 THEN email2
                        ...
                        WHEN 22 THEN email22 END
        mobileno = CASE id WHEN 1 THEN 00000
                           WHEN 2 THEN 11111
                           ...
                           WHEN 22 THEN 2222222222 END
    WHERE
        id BETWEEN 1 AND 22;

Upvotes: 0

Views: 166

Answers (2)

user330315
user330315

Reputation:

I would use a VALUES clause (similar to an INSERT statement):

UPDATE table_name t
  SET email = v.email, 
      mobileno = v.mobileno 
from ( 
  values 
    (1, 'email1', '00001'),
    (2, 'email2', '00002'),
    (3, 'email3', '00003'),
    ....
    (22, 'email22', '00022')
) as v(id, email, mobileno)
where t.id = v.id;

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520918

The best option might be to create a separate table which contains the desired email and mobile values, based on some id. Lacking this, we could use a big CASE expression here:

UPDATE table_name
SET
    email = CASE id WHEN 1 THEN email1,
                    WHEN 2 THEN email2,
                    ...
                    WHEN 22 THEN email22 END,
    mobileno = CASE id WHEN 1 THEN 00000,
                       WHEN 2 THEN 11111,
                       ...
                       WHEN 22 THEN 2222222222 END
WHERE
    id BETWEEN 1 AND 22;

Assuming you did have a table with the id values and emails/mobile numbers, you could do an update join here:

UPDATE table_name AS t1
SET email = t2.email, mobileno = t2.mobileno
FROM temp_table_name t2
WHERE t1.id = t2.id;

Upvotes: 0

Related Questions