Updating multiple tables sql server
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.
Similarly, let’s say we want to update two fields, salary and bonus.
If we want to update more than one column, whereas previously we specified the new value using a scalar, when there is more than one column to update, we use an Array Ref.
So if the caller has a Postgre SQL database, and calls with data to represent our third example (where the target values are all unique), then the Postgre SQL-specific subclass will effect the updates using the table / UPDATE … To match on names we now need to match on two columns.
So whereas previously we specified the matching column using a scalar, when there is more than one column to match, we use an Array Ref.
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.
Also, let’s say we want to make updates only for people in the company “Widget Co”.
“Bob Jones” appears in both companies, so we need to be careful to identify the company, but we don’t want to have to specify it repeatedly for each row.
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.