Informix Guide to SQL: Tutorial Modifying Data

Use the UPDATE statement to change the contents of one or more columns in one or more existing rows of a table. This statement takes two fundamentally different forms. One lets you assign specific values to columns by name; the other lets you assign a list of values (that might be returned by a SELECT statement) to a list of columns. In either case, if you are updating rows, and some of the columns have data integrity constraints, the data you change must be within the constraints placed on those columns. For more information, refer to Data Integrity.

Either form of the UPDATE statement can end with a WHERE clause that determines which rows are modified. If you omit the WHERE clause, all rows are modified. The WHERE clause can be quite complicated to select the precise set of rows that need changing. The only restriction on the WHERE clause is that the table that you update cannot be named in the FROM clause of a subquery.

The first form of an UPDATE statement uses a series of assignment clauses to specify new column values, as the following example shows:

UPDATE customer

	SET fname = 'Barnaby', lname = 'Dorfler'

	WHERE customer_num = 103

The WHERE clause selects the row you want to update. In the demonstration database, the customer.customer_num column is the primary key for that table, so this statement can update no more than one row.

You can also use subqueries in the WHERE clause. Suppose that the Anza Corporation issues a safety recall of their tennis balls. As a result, any unshipped orders that include stock number 6 from manufacturer ANZ must be put on back order, as the following example shows:

UPDATE orders

	SET backlog = 'y'

	WHERE ship_date IS NULL

	AND order_num IN

		(SELECT DISTINCT items.order_num FROM items

			WHERE items.stock_num = 6

			AND items.manu_code = 'ANZ')

This subquery returns a column of order numbers (zero or more). The UPDATE operation then tests each row of orders against the list and performs the update if that row matches.

Each assignment after the keyword SET specifies a new value for a column. That value is applied uniformly to every row that you update. In the examples in the previous section, the new values were constants, but you can assign any expression, including one based on the column value itself. Suppose the manufacturer code HRO has raised all prices by 5 percent, and you must update the stock table to reflect this increase. Use the following statement:

UPDATE stock

	SET unit_price = unit_price * 1.05

	WHERE manu_code = 'HRO'

You can also use a subquery as part of the assigned value. When a subquery is used as an element of an expression, it must return exactly one value (one column and one row). Perhaps you decide that for any stock number, you must charge a higher price than any manufacturer of that product. You need to update the prices of all unshipped orders. The SELECT statements in the following example specify the criteria:

UPDATE items

	SET total_price = quantity *

		(SELECT MAX (unit_price) FROM stock

			WHERE stock.stock_num = items.stock_num)

	WHERE items.order_num IN

		(SELECT order_num FROM orders

			WHERE ship_date IS NULL)

The first SELECT statement returns a single value: the highest price in the stock table for a particular product. The first SELECT statement is a correlated subquery because, when a value from items appears in the WHERE clause for the first SELECT statement, you must execute it for every row that you update.

The second SELECT statement produces a list of the order numbers of unshipped orders. It is an uncorrelated subquery that is executed once.

Restrictions exist on the use of subqueries when you modify data. In particular, you cannot query the table that is being modified. You can refer to the present value of a column in an expression, as in the example that increments the unit_price column by 5 percent. You can also refer to a value of a column in a WHERE clause in a subquery, as in the example that updated the stock table, in which the items table is updated and items.stock_num is used in a join expression.

Enterprise Decision Server does not allow you to use a subquery in the SET clause of an UPDATE statement.

The need to update and query a table at the same time does not occur often in a well-designed database. (For more information about database design, see the Informix Guide to Database Design and Implementation.) However, you might want to update and query at the same time when a database is first being developed, before its design has been carefully thought through. A typical problem arises when a table inadvertently and incorrectly contains a few rows with duplicate values in a column that should be unique. You might want to delete the duplicate rows or update only the duplicate rows. Either way, a test for duplicate rows inevitably requires a subquery on the same table that you want to modify, which is not allowed in an UPDATE statement or DELETE statement. Chapter 8 discusses how to use an update cursor to perform this kind of modification.

The second form of UPDATE statement replaces the list of assignments with a single bulk assignment, in which a list of columns is set equal to a list of values. When the values are simple constants, this form is nothing more than the form of the previous example with its parts rearranged, as the following example shows:

UPDATE customer

	SET (fname, lname) = ('Barnaby', 'Dorfler')

	WHERE customer_num = 103

No advantage exists to writing the statement this way. In fact, it is harder to read because it is not obvious which values are assigned to which columns.

However, when the values to be assigned come from a single SELECT statement, this form makes sense. Suppose that changes of address are to be applied to several customers. Instead of updating the customer table each time a change is reported, the new addresses are collected in a single temporary table named newaddr. It contains columns for the customer number and the address-related fields of the customer table. Now the time comes to apply all the new addresses at once.

UPDATE customer
SET (address1, address2, city, state, zipcode) =
((SELECT address1, address2, city, state, zipcode
FROM newaddr
WHERE newaddr.customer_num=customer.customer_num))
WHERE customer_num IN
(SELECT customer_num FROM newaddr)

A single SELECT statement produces the values for multiple columns. If you rewrite this example in the other form, with an assignment for each updated column, you must write five SELECT statements, one for each column to be updated. Not only is such a statement harder to write but it also takes much longer to execute.

Tip: In SQL API programs, you can use record or host variables to update values. For more information, refer to Chapter 7.

Updating Row Types

The syntax you use to update a row-type value differs somewhat depending on whether the column is a named row type or unnamed row type. This section describes those differences and also describes how to specify null values for the fields of a row type.

To update a column that is defined on a named row type, you must specify all fields of the row type. For example, the following statement updates only the street and city fields of the address column in the employee table, but each field of the row type must contain a value (null values are allowed):

UPDATE employee
	SET address = ROW('103 California St', 

	'San Francisco', address.state, address.zip)::address_t
	WHERE name = 'zawinul, joe'

In this example, the values of the state and zip fields are read from and then immediately reinserted into the row. Only the street and city fields of the address column are updated.

When you update the fields of a column that are defined on a named row type, you must use a ROW constructor and cast the row value to the appropriate named row type.

To update a column that is defined on an unnamed row type, you must specify all fields of the row type. For example, the following statement updates only the street and city fields of the address column in the student table, but each field of the row type must contain a value (null values are allowed):

UPDATE student
	SET s_address = ROW('13 Sunset', 'Fresno', 
	s_address.state, s_address.zip)
	WHERE s_name = 'henry, john'

To update the fields of a column that are defined on an unnamed row type, always specify the ROW constructor before the field values to be inserted.

The fields of a row-type column can contain null values. When you insert into or update a row-type field with a null value, you must cast the value to the data type of that field.

The following UPDATE statement shows how you might specify null values for particular fields of a named row-type column:

UPDATE employee
	SET address = ROW(NULL::VARCHAR(20), 'Davis', 'CA',
	ROW(NULL::CHAR(5), NULL::CHAR(4)))::address_t)
	WHERE name = 'henry, john'

The following UPDATE statement shows how you specify null values for the street and zip fields of the address column for the student table.

UPDATE student
	SET address = ROW(NULL::VARCHAR(20), address.city,
	address.state, NULL::VARCHAR(9))
	WHERE s_name = 'henry, john'


	Important: You cannot specify null values for a row-type column.You can only specify null values for the individual fields of the row type.

When you use DB-Access or the Relational Object Manager to update a collection type, you must update the entire collection. The following statement shows how to update the projects column. To locate the row that needs to be updated, use the IN keyword to perform a search on the direct_reports column.

UPDATE manager
SET projects = "LIST 
{
	ROW('brazil_project', SET{'Pryor', 'Murphy', 'Kinsley',
		'Bryant'}), 
	ROW ('cuba_project', SET{'Forester', 'Barth', 'Lewis',
		'Leonard'})
}"
WHERE 'Williams' IN direct_reports

The first occurrence of the SET keyword in the preceding statement is part of the UPDATE statement syntax.

Tip: Do not confuse the SET keyword of an UPDATE statement with the SET constructor that indicates that a collection is a SET.

Although you can use the IN keyword to locate specific elements of a simple collection, you cannot update individual elements of a collection column from DB-Access or the Relational Object Manager. However, you can create ESQL/C programs and SPL routines to update elements within a collection. For information about how to create an ESQL/C program to update a collection, see the Informix ESQL/C Programmer's Manual. For information about how to create SPL routines to update a collection, see the section Handling Collections.

When you update the rows of a supertable, the scope of the update is a supertable and its subtables.

When you construct an UPDATE statement on a supertable, you can update all columns in the supertable and columns of subtables that are inherited from the supertable. For example, the following statement updates rows from the employee and sales_rep tables, which are subtables of the supertable person:

UPDATE person
	SET salary=65000
	WHERE address.state = 'CA'

However, an update on a supertable does not allow you to update columns from subtables that are not in the supertable. For example, in the previous update statement, you cannot update the region_num column of the sales_rep table because the region_num column does not occur in the employee table.

When you perform updates on supertables, be aware of the scope of the update. For example, an UPDATE statement on the person table that does not include a WHERE clause to restrict which rows to update, modifies all rows of the person, employee, and sales_rep table.

To limit an update to rows of the supertable only, you must use the ONLY keyword in the UPDATE statement. For example, the following statement updates rows of the person table only:

UPDATE ONLY(person)
	SET address = ROW('14 Jackson St', 'Berkeley',
	address.state, address.zip)
	WHERE name = 'Sallie, A.'


Warning: Use caution when you update rows of a supertable because the scope of an update on a supertable includes the supertable and all its subtables.

The CASE expression allows a statement to return one of several possible results, depending on which of several condition tests evaluates to TRUE.

The following example shows how to use a CASE statement in an UPDATE statement to increase the unit price of certain items in the stock table:

UPDATE stock
	SET unit_price = CASE
		WHEN stock_num = 1 
		AND manu_code = "HRO"
		THEN unit_price = unit_price * 1.2
		WHEN stock_num = 1 
		AND manu_code = "SMT"
		THEN unit_price = unit_price * 1.1
		ELSE 0
		END

You must include at least one WHEN clause within the CASE expression; subsequent WHEN clauses and the ELSE clause are optional. If no WHEN condition evaluates to true, the resulting value is null.

You can use an SQL function that you can call from within an UPDATE statement to import and export smart large objects. For a description of these functions, see page 4-20.

The following UPDATE statement uses the LOCOPY() function to copy BLOB data from the mugshot column of the fbi_list table into the picture column of the inmate table. (Figure 4-2 on page 4-20 defines the inmate and fbi_list tables.)

UPDATE inmate (picture)
    SET picture = (SELECT LOCOPY(mugshot, 'inmate', 'picture')
                   FROM fbi_list WHERE fbi_list.id = 669)
    WHERE inmate.id_num = 437

The first argument for LOCOPY() specifies the column (mugshot) from which the object is exported. The second and third arguments specify the name of the table (inmate) and column (picture) whose storage characteristics the newly created object will use. After execution of the UPDATE statement, the picture column contains data from the mugshot column.

When you specify the path of a filename in the function argument, apply the following rules:

• If the source file resides on the server computer, you must specify the full pathname to the file (not the pathname relative to the current working directory).
• If the source file resides on the client computer, you can specify either the full or relative pathname to the file.

Enterprise Decision Server allows you to use a join on tables to determine which columns to update. You can use columns from any table that you list in the FROM clause in the SET clause to specify values for the columns and rows to update.

When you use the FROM clause, you must include the name of the table in which the update is to be performed. Otherwise, an error results. The following example illustrates how you can use the UPDATE statement with a FROM clause:

UPDATE t SET a = t2.a FROM t, t2 WHERE t.b = t2.b

In the preceding example, the statement performs the same action as it does when you omit the FROM clause altogether. You are allowed to specify more than one table in the FROM clause of the UPDATE statement. However, if you specify only one table, it must be the target table.