Sql updating multiple rows based on a select statement
But if there are a large number of rows that require an update, then the overhead of issuing large numbers of UPDATE statements can result in the operation as a whole taking a long time to complete.The traditional advice for improving performance for multiple UPDATE statements is to “prepare” the required query once, and then “execute” the prepared query once for each row requiring an update.In this case there are two distinct values, so we can do it in two UPDATE statements: So we can reduce the number of queries from five to two. But the extent to which this helps us reduces as the proportion of distinct SET values goes up.Eventually every SET value is different, for example: UPDATE staff SET salary = 1125 WHERE name = ' Bob'; UPDATE staff SET salary = 1200 WHERE name = ' Jane'; UPDATE staff SET salary = 1100 WHERE name = ' Frank'; UPDATE staff SET salary = 1175 WHERE name = ' Susan'; UPDATE staff SET salary = 1150 WHERE name = ' John'; Since every SET clause is now different, we can’t coalesce any queries using the approaches mentioned before, so we still have one UPDATE statement for every row requiring changes. What if we had a database table which contained a representation of the updates we wanted to make, e.g.So, given a list of updates to apply we could effect them using the following steps: So in the example above we can reduce five statements to four. But now the number of statements is no longer directly dependent on the number of rows requiring updates.Even if we wanted to update a thousand rows with different values, we could still do it with four statements.
This is the intention of 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'; “key_columns” specifies the columns which will be used to identify rows which need to be updated (using WHERE). The first element provides the value of the column (specified by “key_columns”) to identify the row to be updated.
The ‘where’ parameter can be used to specify a global condition, e.g. Another thing which we may want to do is update a column with the same value for all rows being updated. Rather than have to specify the same value over and over again, we can use the ‘set’ parameter.
The ‘set’ parameter takes a Hash Ref whose keys are the columns to be updated.
In the case of Postgre SQL, it can be done using a FROM clause with UPDATE, like this: This does an INNER JOIN between the tables “staff” and “updates” where the column “name” matches.
The SET clause then takes the “salary” field from the “updates” table and uses it to update the “salary” field of the “staff” table.