Bulk update of all columns

Question

I am wondering if PostgreSQL has an update query somewhat like their insert values syntax.

I have an updated set of data in this form currently:

INSERT INTO bought_in_control_panel(ID,PARENT_ID,BOUGHT_IN_FORM_TYPE_ID,PRIORITY,NAME,HEADING,DESCRIPTION,ICON,BOUGHT_IN_CONTROL_PANEL_FILE_ID)
VALUES(109,1,28,100,'Tooling','Tooling','Enter your Machine Tools here','tooling.png',null);

and I want to update the rows that are there already to the new data, I'm looking for something like the following where I can update all rows without repeating myself:

UPDATE ON ID
bought_in_control_panel(ID,PARENT_ID,BOUGHT_IN_FORM_TYPE_ID,PRIORITY,NAME,HEADING,DESCRIPTION,ICON,BOUGHT_IN_CONTROL_PANEL_FILE_ID) 
VALUES(
        (109,1,28,100,'Tooling','Tooling','Enter your Machine Tools here','tooling.png',null), 
        (1,0,1,200,'Bought In','Bought In','','boughtin.png',null)
);

The above would check for matching ID values, and update the matches with the new data.

Note: I am really looking to avoid manually mapping all the column names. PostgreSQL already knows my columns, and I've already mapped them on the data. Why do that again in a longer format?

Answer 1

If you only want to update data, I'm not sure what the INSERT statement is for in your question.

If you just want to update several rows with a single statement, you might be looking for this:

with update_values (ID,PARENT_ID,BOUGHT_IN_FORM_TYPE_ID,PRIORITY,NAME,HEADING,DESCRIPTION,ICON,BOUGHT_IN_CONTROL_PANEL_FILE_ID) as 
(
  VALUES
     (109,1,28,100,'Tooling','Tooling','Enter your Machine Tools here','tooling.png',null), 
     (1,0,1,200,'Bought In','Bought In','','boughtin.png',null)
)
update bought_in_control_panel
   set parent_id = ud.parent_id, 
       bought_in_form_type_id = ud.bought_in_form_type_id,
       ....
from update_values ud
where ud.id = bought_in_control_panel.id;

Answer 2

There is actually a shorter syntax variant for this, available since Postgres 8.2 (released 2006!). The release notes:

• Allow UPDATE to set multiple columns with a list of values (Susanne Ebrecht)

This is basically a short-hand for assigning the columns and values in pairs. The syntax is UPDATE tab SET (column, ...) = (val, ...).

Applied to your query:

UPDATE bought_in_control_panel b
SET   (  parent_id,   bought_in_form_type_id,   priority,   name,   heading,   description,   icon,   bought_in_control_panel_file_id) 
    = (v.parent_id, v.bought_in_form_type_id, v.priority, v.name, v.heading, v.description, v.icon, v.bought_in_control_panel_file_id) 
FROM (
   VALUES
      (109,1,28,100,'Tooling','Tooling','Enter your Machine Tools here','tooling.png',null)
    , (1,0,1,200,'Bought In','Bought In','','boughtin.png',null)
   ) AS v(id,parent_id,bought_in_form_type_id,priority,name,heading
         ,description,icon,bought_in_control_panel_file_id)
WHERE  b.id = v.id;

You still have to list all columns, but you can trim some noise and it's easier to assemble a list, copy it and prepend the table alias of the source table. You can also do this dynamically. See below.

Also using a subquery, which works just as well as a CTE here, but with a bit less overhead (performance).

You can even mix lists and single columns freely, if you feel inclined to do so. No difference in the result, but might come in handy for formatting or, more importantly, for dynamically generated commands:

UPDATE bought_in_control_panel b
SET    parent_id = v.parent_id
    , (  bought_in_form_type_id,   priority,   name,   heading) 
    = (v.bought_in_form_type_id, v.priority, v.name, v.heading) 
    , (  description,   icon,   bought_in_control_panel_file_id) 
    = (v.description, v.icon, v.bought_in_control_panel_file_id) 
FROM (
   VALUES
      (109,1,28,100,'Tooling','Tooling','Enter your Machine Tools here','tooling.png',null)
    , (1,0,1,200,'Bought In','Bought In','','boughtin.png',null)
   ) AS v(id,parent_id,bought_in_form_type_id,priority,name,heading
         ,description,icon,bought_in_control_panel_file_id)
WHERE  b.id = v.id;

It's all in the manual if you look closely.

Speaking of dynamic SQL, this related answer on SO demonstrates how to fetch the list of columns from the system catalog pg_attribute and execute it all in a function:

• Update multiple columns in a trigger function in plpgsql

Answer 3

IIRC postgres doesn't have MERGE or similar (though it does seem to be being talked about for later versions: http://wiki.postgresql.org/wiki/SQL_MERGE).

If you know all the rows in question exist you could do something like:

UPDATE bicp 
SET    PARENT_ID                       = newdata.PARENT_ID                       
     , BOUGHT_IN_FORM_TYPE_ID          = newdata.BOUGHT_IN_FORM_TYPE_ID          
     , PRIORITY                        = newdata.PRIORITY                        
     , NAME                            = newdata.NAME                            
     , HEADING                         = newdata.HEADING                         
     , DESCRIPTION                     = newdata.DESCRIPTION                     
     , ICON                            = newdata.ICON                            
     , BOUGHT_IN_CONTROL_PANEL_FILE_ID = newdata.BOUGHT_IN_CONTROL_PANEL_FILE_ID 
FROM   (    SELECT ID                              = 109
                 , PARENT_ID                       = 1
                 , BOUGHT_IN_FORM_TYPE_ID          = 28
                 , PRIORITY                        = 100
                 , NAME                            = 'Tooling'
                 , HEADING                         = 'Tooling'
                 , DESCRIPTION                     = 'Enter you Machine Tools Here'
                 , ICON                            = 'tooling.png'
                 , BOUGHT_IN_CONTROL_PANEL_FILE_ID = CAST(NULL AS INT)
           UNION ALL SELECT 1,0,1,200,'Bought In','Bought In','','boughtin.png',null
       ) AS newdata 
JOIN   bought_in_control_panel AS bicp ON newdata.ID = bicp.ID

adding an extra UNION for each subsequent row that needs updating, though that us rather long-winded for just a couple of updates and may not be terribly efficient (that set of UNIONs producing "newdata" will get thrown into a temporary table, potentially on disk, and there will be no index on ID in that temporary table which may be significant if there are many rows).

NOTE: The cast for BOUGHT_IN_CONTROL_PANEL_FILE_ID is to ensure the query planner knows the type (I've seen this cause trouble in MSSQL with it deciding early that a NULL should be an INT then finding a character type in that position in a later UNION clause - so I always specify a column type for literal NULL values when using UNION)

To make the first row the same format as the subsequent ones, to make programitically generating this sort of statement slightly easier, this hack will do the trick in MSSQL:

UPDATE bicp 
SET    PARENT_ID                       = newdata.PARENT_ID                       
     , BOUGHT_IN_FORM_TYPE_ID          = newdata.BOUGHT_IN_FORM_TYPE_ID          
     , PRIORITY                        = newdata.PRIORITY                        
     , NAME                            = newdata.NAME                            
     , HEADING                         = newdata.HEADING                         
     , DESCRIPTION                     = newdata.DESCRIPTION                     
     , ICON                            = newdata.ICON                            
     , BOUGHT_IN_CONTROL_PANEL_FILE_ID = newdata.BOUGHT_IN_CONTROL_PANEL_FILE_ID 
FROM   (    SELECT ID                              = CAST(NULL AS INT)
                 , PARENT_ID                       = CAST(NULL AS INT)
                 , BOUGHT_IN_FORM_TYPE_ID          = CAST(NULL AS INT)
                 , PRIORITY                        = CAST(NULL AS INT)
                 , NAME                            = CAST(NULL AS NVARCHAR(MAX))
                 , HEADING                         = CAST(NULL AS NVARCHAR(MAX))
                 , DESCRIPTION                     = CAST(NULL AS NVARCHAR(MAX))
                 , ICON                            = CAST(NULL AS NVARCHAR(MAX))
                 , BOUGHT_IN_CONTROL_PANEL_FILE_ID = CAST(NULL AS INT)
           UNION ALL SELECT 109, 1, 28, 100, 'Tooling'  , 'Tooling'  , 'Enter your Machine Tools here', 'tooling.png' , null
           UNION ALL SELECT 1  , 0, 1 , 200, 'Bought In', 'Bought In', ''                             , 'boughtin.png', null
       ) AS newdata 
JOIN   bought_in_control_panel AS bicp ON newdata.ID = bicp.ID

Caveat 1: this should be valid syntax for MSSQL, but I didn't test it and I don't know how standard all the syntax is so you might need tweaks for postgres (for instance not all query interpreters let you get away with not naming the columns after the first SELECT in a sequence of UNIONs).

Caveat 2: this feels somewhat hacky, and is considerably more verbose than the ideal in your example.

As an alternative, drop the new data into a temporary table and run two statements to update rows that do exist in the current data (then insert those that don't, if you are trying to UPSERT rather than just UPDATE), wrapping this in an explicit transaction to ensure that you get all-or-nothing consistency for the operation - that would likely be a lot cleaner despite not being a single statement.