Question:
I have two related tables. I need to go and update atable searching for a value I find in the second table (byjoining both tables). I wrote the following query, but endedup with a syntax error:

UPDATE Software, PckgSET 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 nameof 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.