Peter Penzov Peter Penzov - 6 months ago 22
SQL Question

Update all rows with one SQL query

I'm using this PostgreSQL table to store configuration variables:

CREATE TABLE SYS_PARAM(
SETTING_KEY TEXT NOT NULL,
VALUE_TYPE TEXT,
VALUE TEXT
)
;


enter image description here

How I can update all configuration settings values using one SQL statement?

Answer

If you plan on performing these updates more than once or twice over time, it would be good to have a function handle this for you. You could use the table itself as a type for a variadic parameter within a function, like so:

-- The function
CREATE OR REPLACE FUNCTION update_sys_param(VARIADIC params sys_param[])
RETURNS VOID
AS $$
BEGIN

  UPDATE sys_param
    SET value_type = upd.value_type, value = upd.value
  FROM
    sys_param src
  INNER JOIN
    UNNEST(params) upd
    ON  (src.setting_key = upd.setting_key);

END; $$ LANGUAGE PLPGSQL;

-- To call it
SELECT update_sys_param(('SMTP_PORT','int','123'),('SMTP_OTHER','text','435343'));

However, if this is a one-time update you can try either of these two:

UPDATE using JOIN

UPDATE sys_param
   SET
     value_type = new.value_type,
     value = new.value
FROM
   sys_param src
INNER JOIN
   new_params new --< this table/view/cte must already exist or you must create it.
   ON  (src.setting_key = new.setting_key);

UPDATE using CASE

UPDATE sys_param
SET value = CASE setting_key
      WHEN 'SMTP_PORT' THEN '2100'
      (..and so on..)
      END;
-- You would need to repeat the case statement if you intend on updating the value_type, too.