Skip to content

Mr SQL's Blog

Musings on Microsoft SQL Server

Tag Archives: Oracle

As you are probably aware, updating data in a table can be achieved with a relatively simple SQL statement. In the following example, the data we want to change is included in the statement itself:-

UPDATE employees
SET last_name = 'Smith'
WHERE employee_id = 6392

We feed in ‘Smith’ as the new data to be entered in the last_name column, and we specify ‘6329’ as the record to be updated. This works well, but is usually only useful for tasks where only a few records need to be updated, or an entire column needs to be updated with the same data (achieved by excluding the WHERE clause).

Much more useful is the ability to UPDATE data based on the contents of another table or data source. This allows us to update large amounts of data and ensures its integrity. There are two approaches for performing updates based on data from another table.

METHOD 1

The example below updates the primary_address in the employees table to that of the home_address in the addresses table. The WHERE clause ensures that the rows in the employees table are updated with the correct data from the addresses table by joining on the employee_id column, which is present in both tables.

UPDATE employees
SET e.primary_address = a.home_address
FROM employees e, addresses a
WHERE e.employee_id = a.employee_id

With this method, it is possible to update additional columns and to expand the WHERE clause to limit the records which are altered. In the following, we add an additional two columns to be updated and a sub-query to limit the update to those staff who have commenced employment within the last year:-

UPDATE employees
SET e.primary_address = a.home_address,
e.secondary_address = a.next_of_kin_address,
e.primary_telephone = a.home_telephone
FROM employees e, addresses a
WHERE e.employee_id = a.employee_id
AND e.employee_id IN
(SELECT employee_id
FROM employment_dates
WHERE [start_date] > DATEADD(YEAR, -1, GETDATE()))


METHOD 2

This approach uses a sub-query to select the home address from the addresses table. Don’t forget to include the WHERE EXISTS clause, followed by the repetition of the sub-query, otherwise you will receive errors – this is a common mistake!

UPDATE employees
SET e.primary_address =
(SELECT a.home_address
FROM addresses a
WHERE e.employee_id = a.employee_id)
WHERE EXISTS (SELECT a.home_address
FROM addresses a
WHERE e.employee_id = a.employee_id)

As you can see from this statement above, the second method requires more code and in my opinion is much more difficult to understand. Therefore, I tend to use the first method in most circumstances. An additional limitation is that it is not easy to update multiple columns. Oracle (and a number of other database management systems) allows multiple columns to be updated by specifying the columns within brackets:-

UPDATE employees
SET (e.primary_address, e.secondary_address, e.primary_telephone) =
(SELECT a.home_address, a.next_of_kin_address, a.home_telephone
FROM addresses a
WHERE e.employee_id = a.employee_id)
WHERE EXISTS (SELECT a.home_address, a.next_of_kin_address, a.home_telephone
FROM addresses a
WHERE e.employee_id = a.employee_id)

However, the above does not work with SQL Server (at least not in 2005)!

To achieve this with SQL Server, you would need to write the query for each column you want to update.

UPDATE employees
SET e.primary_address =
(SELECT a.home_address
FROM addresses a
WHERE e.employee_id = a.employee_id)
WHERE EXISTS (SELECT a.home_address
FROM addresses a
WHERE e.employee_id = a.employee_id),
e.secondary_address =
(SELECT a.next_of_kin_address
FROM addresses a
WHERE e.employee_id = a.employee_id)
WHERE EXISTS (SELECT a.next_of_kin_address
FROM addresses a
WHERE e.employee_id = a.employee_id),
e.primary_telephone =
(SELECT a.home_telephone
FROM addresses a
WHERE e.employee_id = a.employee_id)
WHERE EXISTS (SELECT a.home_telephone
FROM addresses a
WHERE e.employee_id = a.employee_id)

NOT NICE! And this does not even include the clause to limit the updates to recently employed staff.

Tags: , , , ,

%d bloggers like this: