Question:
Oracle 7.3, Microsoft Access 2.0 and up, and Microsoft SQL Server 6.5 all provide methods by which a single SQL command can update a table using multiple values looked up in another table (i.e. via a join). Oracle provides update-able join VIEWs. Access offers INNER JOIN on the UPDATE command, borrowed from the new ANSI SELECT syntax, and SQL Server adds a FROM clause to the UPDATE command.

Informix 7.1 doesn’t appear to support any of these, and the documentation’s discussion on multiple table VIEWs makes a vague reference to anomalies from attempting to update such VIEWs.

Is there a simple, SQL-only way to do such an update with Informix?

Answer:
Yes, you can do an update in Informix SQL based on columns in other tables; you simply need to use subqueries. A subquery will allow you to retrieve values from other tables to use either in your values clause or your where clause.

As an example, suppose we wanted to set a value to “UNKNOWN” for every value in a table which did not have a corresponding value in another table.

update mytable set code_status = “UNKNOWN”           where mytable.code not in (select code from code_table)

You can also set the value to be updated in a table, based on information from another table. In the following update, we set the value of a field in one table, based on the values in both the update table and the subquery table.

update mytable set code_status =                           (select code_status from code_table                              where code_table.code = mytable.code)          where mytable.code in (select code from code_table)

You will frequently end up using correlated subqueries when performing these types of updates and joins. A correlated subquery is a subquery whose result depends on a value in the outer query row (select, update or delete).

Whether I am doing an update or a delete, I always test the statement by using a select before doing the update or delete. This can save you a good amount of grief when you get unexpected results from your subquery and you end up updating or deleting the wrong rows. As carpenters say, “measure twice, cut once.” Update and delete errors can be difficult or even impossible to recover from! As an example, to convert the above update statement into a select is very simple:

select mytable.code, (select code_status from code_table                              where code_table.code = mytable.code)          from mytable        where mytable.code in (select code from code_table)

Once this select statement returns the values you expect, go ahead and change it back into an update or delete statement.

One of the more confusing aspects of doing this type of update is when the join between two tables requires more than a single column. An example of how to perform such an update is:

update sales set salesperson = “Fred”        where sales_id = (select sales_id from employee                                    where employee.sales_id = sales.sale_id                              and employee.type = sales.sale_type)

As you can see, we do both parts of the join within the subquery using values from the outer query. So both values must be equivalent in order for the table to be updated.

Do not make the mistake of substituting this with something like:

update sales set salesperson = “Fred”        where sales_id = (select sales_id from employee                                    where employee.sales_id = sales.sale_id)            and sale_type = (select sale_type from employee                             where employee.type = sales.sale_type)

This update statement will end up updating more rows than the one above it. The reason is that a row will be updated every time both of these clauses evaluate to true. But because each of these equalities will be evaluated independently, as long as any row in the employee table has a matching sales_id and any row in the employee table has a matching sales_type, the update will occur. In the previous update, the update would only occur when a matching sales_id and sales_type were found in the same row of the employee table. This is an easy mistake to make, so be careful.

As a side note, it also easy to do inserts based on values in other tables. For instance:

 insert into mytable (name, code, status)          (select name, code, status from yourtable where value = 3)

These update, delete and insert techniques are a very powerful tool in Informix SQL.