SQL/MX Quick Start (G06.24+, H06.03+)

Changing Information in a Table

HP NonStop SQL/MX Quick Start—523724-002

6-2

Updating an Existing Row

To change values in one or more columns and one or more rows, use the UPDATE

statement.

Example

In the PARTS table, you have inserted a row that describes a data modem with part

number 9999. Suppose you determine that the quantity available of part number 9999

is 12 units. You also want to enter a more specific description of the data modem:

UPDATE PARTS

SET PARTDESC = '1200 BD DATA MODEM', QTY_AVAILABLE = 12

WHERE PARTNUM = 9999;

--- 1 row(s) updated.

Example

If you want to update all rows, omit the WHERE clause. Double the price of every part:

UPDATE PARTS SET PRICE = PRICE * 2;

--- 31 row(s) updated.

Example

If you actually changed your sample database by executing the previous UPDATE

statement, execute this statement to return the PRICE column to its previous values:

UPDATE PARTS SET PRICE = PRICE * .5;

--- 31 row(s) updated.

Tip

•

The value you specify for a column of the SET list can be an expression, but the

expression cannot include any aggregate functions, such as AVG. The expression

can refer to any column in the row you are updating.

•

A table can have a primary key consisting of one or more columns that uniquely

identify each row in the table. You cannot update the value of a column that is part

of the primary key. Instead, you must delete the row with the old primary key and

then insert the row with the new primary key.