Updating Through a Join with SQL, Take Two

Published: April 27, 2011

Hey, Ted:

I am studying your tip Updating through a Join with SQL, and something escapes me. I do not understand the redundancy in the first code example. Do all updates that involve joins have to have redundant code?

--Ron

I know it's confusing, Ron. I can go into more detail for you.

This is the example to which you refer:

update customer as c
   set region =
      (select newregion
         from fixes
        where oldregion = c.region)
 where exists
      (select *
         from fixes
        where oldregion = c.region)

You're right that there is some redundancy. Both the SET and WHERE clauses of the UPDATE statement refer to the FIXES table.

         from fixes
        where oldregion = c.region)

The direct answer to your question is "no". Updates that involve joins do not necessarily have to have redundant code. It depends on what you wish to accomplish. If you want the UPDATE to change all records (rows) in the database file (table or view), then there is no need for a WHERE clause, because the purpose of the WHERE clause is to specify which rows are to be updated. But if you only want to update some of the rows, then yes, you must include a WHERE clause in the UPDATE statement.

Let me illustrate.

Suppose you have a master file of vendors that includes a vendor-type code. The code has never been loaded, but now you need to update the code based on a file that was created from a user's spreadsheet. The master file is VENDORS. The file of updates is called VENDORUPDT, and it has two fields only: vendor ID number and type code. Let's consider some scenarios.

1. VENDORUPDT has a row for each vendor. All rows in VENDOR will be updated.

In this case, there is no need for a WHERE clause in the UPDATE statement.

update vendors as v1
   set v1.type =
         (select v2.type
            from vendorupdt as v2
           where v2.id = v1.id)

2. VENDORUPDT does not have a row for some vendors. All rows in VENDOR will be updated. Vendors who are not referenced in VENDORUPDT will be given a null type code.

Since all rows are to be updated, there is no WHERE clause in the UPDATE.

update vendors as v1
   set v1.type =
         (select v2.type
            from vendorupdt as v2
           where v2.id = v1.id)

3. VENDORUPDT does not have a row for some vendors. All rows in VENDOR will be updated. Vendors who are not referenced in VENDORUPDT will be given a type value of 2.

Since all rows are to be updated, there is no WHERE clause in the UPDATE.

update vendors as v1
   set v1.type = coalesce(
         (select v2.type
            from vendorupdt as v2
           where v2.id = v1.id), 2)

4. VENDORUPDT does not have a row for some vendors. Rows in VENDOR will be updated only if they are found in VENDORUPDT.

Since some rows will not be updated, the UPDATE needs a WHERE clause.

update vendors as v1
   set v1.type = 
         (select v2.type
            from vendorupdt as v2
           where v2.id = v1.id)
 where v1.id in
       (select v3.id
          from vendorupdt as v3)

5. VENDORUPDT does not have a row for some vendors. Rows in VENDOR will be updated only if they are found in VENDORUPDT and if they are located in the state of Mississippi.

Since some rows will not be updated, the UPDATE needs a WHERE clause.

update vendors as v1
   set v1.type = 
         (select v2.type
            from vendorupdt as v2
           where v2.id = v1.id)
 where v1.state = 'MS' 
   and v1.id in
       (select v3.id
          from vendorupdt as v3)

Notice that the last two examples contain WHERE clauses, but not the redundancy you noticed in the example from the previous tip.

To sum it up, the WHERE clause specifies which rows are to be updated. It may be redundant with the expression in SET, but it doesn't have to be.

--Ted

RELATED STORIES

Updating through a Join with SQL

Update One File Based on Another File

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot