Update all rows with one SQL query

Question

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

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

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

Answer 1

you can use where true at the end and it update all rows in your table. for example:

UPDATE table_name set table_column = value where true;

it will be update all rows in one SQL query.

Answer 2

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.