Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: SQL Server
Expertise: Beginner
Mar 24, 1997

UPDATE Table Based on Data in Another Table

Question:
I have two related tables. I need to go and update a table searching for a value I find in the second table (by joining both tables). I wrote the following query, but ended up with a syntax error:
UPDATE Software, Pckg
SET Software.packid='DB34'
WHERE Software.packid=Pckg.packid AND
      Pckg.packname='Manta' AND
      Pckg.packver=1.50;
Software and Pckg are related by packid. Manta is the name of a non-existent database package, version 1.50. I can't figure out why this query is not working. Can you help?

Answer:
Try an UPDATE statement like this instead:

UPDATE Software
    SET packid='DB34'
    WHERE packid IN
        (SELECT packid FROM Pckg
        WHERE packname='Manta' AND
        packver=1.50);
The UPDATE statement can operate on only one table at a time, so you use a subquery construction to find the data from the other table.

Thanks to Joe Celko for an additional solution to this problem: The SQL-92 standard allows you to use scalar subquery expressions anywhere you would use a scalar value. This can be handy for updating one table based on the contents of another.

For example, let's update the prices of software based on a new catalog we just got. Assume that item is the key in both tables:

UPDATE Software
    SET price = (SELECT price
        FROM NewCatalog AS N1
        WHERE Software.item = N1.item)
    WHERE EXISTS (SELECT price
        FROM NewCatalog AS N1
        WHERE Software.item = N1.item);
Here are the important points to remember about this statement:

1) An UPDATE statement cannot use a correlation name or more than one table name in the UPDATE clause. Many vendors allow a correlation name, however. The table name which appears in the UPDATE clause has scope over the rest of the statement.

2) The scalar subquery expression has to return zero or one value; if it returns more than that, you will get an error message and the transaction will rollback. One way to be sure that you always get a single value is to use an aggregate function in the scalar subquery expression.

3) If the scalar subquery expression returns no value (an empty result set), then it is a NULL. This NULL will be assigned to the row in the table being updated.

4) If you want to update only the rows that match a row in the second table, then you have to do what I have shown here. You must add a WHERE clause to the UPDATE statement that checks for the existence of the match.

The best way to do this is not always clear. Some SQL products will run faster if the SELECT clause in an EXISTS predicate has a constant in it, others run faster if it has a SELECT * in it, and others will run faster if it is an exact copy of the scalar subquery expression you used in the SET clause.

DevX Pro
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date