Question 89099 in PostgreSQL Question posted in
The official documentation can be found here.

Update table's different columns with different conditions – Postgresql

I have a sql here:

update table_name
set column1 = new_value1,
    column2 = new_value2
    
where id = 1;

update table_name
set column3 = new_value3,
    column4 = new_value4
    
where id = 3;

update table_name
set column5 = new_value5,
    column6 = new_value6,
    column7 = new_value7
    
where id = 6;

Is there anyway I can make it simpler or cleaner ?

Tags:

2

Answers


  1. 0

    Make a function or trigger using parameters. If you have a list of columns to set and a list of where columns to check, pass arrays as parameters to the function, and build the string using a loop instead of using one statement like below. I simply have variables for each in this example rather than parameters (as variables) in a function.

    Then use dynamic SQL like so.

    DECLARE sc1 varchar:= 'column1';
DECLARE sv1 varchar := 'new_value1';
DECLARE sc2 varchar:= 'column2';
DECLARE sv2 varchar := 'new_value2';
DECLARE wc1 varchar := 'id';
DECLARE wv1 varchar := '1';

EXECUTE 'update table_name set ' || sc1 || ' = ' || sv1 || ', ' || sc2 || ' = ' || sv2 || ' where ' || wc1 || ' = ' || wv1

    or

    create function build_query(sc1 varchar, 
                            sv1 varchar,
                            sc2 varchar,
                            sv2 varchar,
                            wc1 varchar,
                            wv1 varchar)
  returns text
as
$$
begin
    return EXECUTE 'update table_name set ' || sc1 || ' = ' || sv1 || ', ' || sc2 || ' = ' || sv2 || ' where ' || wc1 || ' = ' || wv1 ;
end;
$$
language plpgsql;

    Calling function:

    SELECT build_query('column1', 'new_value1', 'column2', 'new_value2', 'id', '1');
    Login or Signup to reply.
  2. 0

    We could re-write this to all run in the same update statement, where it sets all of the columns for each id matched in the WHERE clause and uses case expressions on each column to decide to set the same value or a new value:

    update table_name
set column1 = case when id = 1 then 'new_value1' else column1 end,
    column2 = case when id = 1 then 'new_value2' else column2 end,
    column3 = case when id = 3 then 'new_value3' else column3 end,
    column4 = case when id = 3 then 'new_value4' else column4 end,
    column5 = case when id = 6 then 'new_value5' else column5 end,
    column6 = case when id = 6 then 'new_value6' else column6 end,
    column7 = case when id = 6 then 'new_value7' else column7 end
where id IN (1, 3, 6);

    We could further change this to use a JOIN expression on a table-valued constructor:

    UPDATE table_name t
INNER JOIN ( VALUES 
   ROW (1, 'new_value1', 'new_value2', NULL, NULL, NULL, NULL, NULL),
   ROW (3, NULL, NULL, 'new_value3', 'new_value4', NULL, NULL, NULL),
   ROW (6, NULL, NULL, NULL, NULL, 'new_value5', 'new_value6', 'new_value7')
) map(id, nv1, nv2, nv3, nv4, nv5, nv6, nv7) ON map.id = t.id
SET t.column1 = coalesce(map.nv1, t.column1),
    t.column2 = coalesce(map.nv2, t.column2),
    t.column3 = coalesce(map.nv3, t.column3),
    t.column4 = coalesce(map.nv4, t.column4),
    t.column5 = coalesce(map.nv5, t.column5),
    t.column6 = coalesce(map.nv6, t.column6),
    t.column7 = coalesce(map.nv7, t.column7);

    Either approach does have the advantage of automatically ensuring everything is inside the same transaction (better atomicity, if that’s even a word), and I like the second option – even though it’s a little more code – for the the way it treats the new values more like data.

    … But

    I wouldn’t call them any "simpler" or "cleaner". What you have already is probably good enough in that regard, unless you really want the single-statement benefits.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search