Updating more than one column

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. OFFICE_ID SQL Error: ORA-01779: cannot modify a column which maps to a non key-preserved table 01779. ='DHB' ) ; merge into FA_SUBLEDGER_MST s1 using ( select SL_CODE, OFFICE_ID, OFFICE_TYPE from EMPLOYEE_MST where OFFICE_TYPE ! So we could think in terms of creating a re-usable module which would implement that logic.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.

There are 3 syntaxes for the UPDATE statement depending on whether you are performing a traditional update or updating one table with data from another table.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.OK, that’s great, we have some theoretical approaches for reducing the number of queries, now what?The code required to implement the above logic is sufficiently fiddly that we would probably not want to have to repeat it.

Leave a Reply