Updating multiple columns using forall

11 Feb

The dominant factor in the time taken to complete the overall operation tends to be the “admin” work in conveying the application’s intention to the database server rather than the actual updates to the database.A more effective solution to this problem is to attempt to reduce the number of UPDATE statements.

In the case where the application server and database server are on different hosts, the round-trip will involve network latency as well.

Let us start with a simple table: UPDATE staff SET salary = 1200 WHERE name = ' Bob'; UPDATE staff SET salary = 1200 WHERE name = ' Jane'; UPDATE staff SET salary = 1200 WHERE name = ' Frank'; UPDATE staff SET salary = 1200 WHERE name = ' Susan'; UPDATE staff SET salary = 1200 WHERE name = ' John'; UPDATE staff SET salary = 1200 WHERE name = ' Bob'; UPDATE staff SET salary = 1250 WHERE name = ' Jane'; UPDATE staff SET salary = 1200 WHERE name = ' Frank'; UPDATE staff SET salary = 1250 WHERE name = ' Susan'; UPDATE staff SET salary = 1200 WHERE name = ' John'; We are no longer setting all the salary fields to the same value, so we can’t collapse it into a single statement.

But we can group the updates according to the value being set, and then do one UPDATE statement per distinct value.

You can post such a request to the Postgres mailing list: postgresql.org/community/lists 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 = v.id; You still have to list all columns, but you can trim some noise and its easier to assemble a list, copy it and prepend the table alias of the source table. You can even 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 = v.id; UPDATE bicp SET PARENT_ID = newdata.

NOTE: The cast for UPDATE bicp SET PARENT_ID = newdata.