skip to content »

sq66.ru

Sql updating multiple columns one update statement

So is structured so that approaches which are generic across different SQL databases are expressed in a base class, and approaches which only work for specific SQL databases are expressed in a subclass.

sql updating multiple columns one update statement-29sql updating multiple columns one update statement-9sql updating multiple columns one update statement-18sql updating multiple columns one update statement-44

Optimising the number of UPDATEs by grouping the distinct SET values can be done in a way which is compatible with most common SQL databases. FROM approach requires knowledge of the specific SQL database being used.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.“value_columns” specifies the columns which will be updated (using SET). The second element provides the value to be SET in the column specified by “value_columns”.In this case, will spot that the target values are all 1200, and will effect the desired changes using a single UPDATE statement as described above.But in many cases this only provides a modest improvement as each UPDATE operation still requires a round-trip communication with the database server.

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.

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.

A requirement arises in many systems to update multiple SQL database rows.