UPDATE statement Format 1 (Update data)
Function
The UPDATE statement updates the values of columns in the rows that satisfy specified search conditions or in the row indicated by a cursor.
Privileges
A user who has the UPDATE privilege for a table can update the row values in that table.
However, if a subquery is specified in the search condition, the user needs the SELECT privilege for the table for which the subquery is specified.
Format 1: Updating rows in a table on a column-by-column basis
UPDATE [authorization-identifier.]table-identifier
[IN (RDAREA-name-specification)][[AS] correlation-name]
[SQL-optimization-specification-for-used-index]
(SET {update-object = update-value
|(update-object,update-object[,update-object]) = row-subquery}
[,{update-object = update-value
|(update-object,update-object[,update-object]...)
= row-subquery}]...
|ADD repetition-column-name [{subscript|*}]
= {ARRAY [element-value[,element-value]...]
|?-parameter|:embedded-variable[:indicator-variable]}
[,repetition-column-name [{subscript|*}]
= {ARRAY [element-value[,element-value]...]
|?-parameter|:embedded-variable
[:indicator-variable]}]...
|DELETE repetition-column-name [{subscript|*}]
[,repetition-column-name [{subscript|*}]]...}
[WHERE {search-condition|CURRENT OF {cursor-name | extended-cursor-name}}]
[WITH ROLLBACK]
update-object ::= {column-name|component-specification
|column-name [{subscript|*}]}
Operands
- [authorization-identifier.]table-identifier
Specifies the table that is to be updated.
The following rules apply:
- In read-only view tables, rows cannot be updated. For details about read-only view tables, see Common rules under CREATE [PUBLIC] VIEW (Define view, define public view) in Chapter 3.
- If updating of a column in a view table is specified, the UPDATE statement updates the base table associated with that column of the view table.
- The table name is valid throughout the entire UPDATE statement.
- A name that is the same as the table name used in the FROM clause of the subquery cannot be specified if the column of the table to be updated is specified in an external reference in a subquery specified in the SET or ADD clause, and a value expression with any of the following attributes is specified in a selection expression in the subquery:
- BLOB
- BINARY type with a maximum length of 32,001 bytes or greater
- Repetition column
- Abstract data type
However, if a view table is specified in the table name in the FROM clause of the subquery, all table names specified in a derived query expression in the view table definition are subject to this operation.
- authorization-identifier
- Specifies the authorization identifier of the owner of the table.
- MASTER cannot be specified as an authorization identifier.
- table-identifier
- Specifies the name of the table that is to be updated.
- [IN (RDAREA-name-specification)]
- IN
- Specifies the RDAREA to access.
- RDAREA-name-specification ::= value-specification
- Of the RDAREAs that contain the table specified in the table identifier, specify the name of the RDAREA to access as a value-specification of type VARCHAR, CHAR, MVARCHAR, or MCHAR. If multiple RDAREA names are specified, separate them with a comma (,). RDAREA names must be unique; an error occurs if duplicate RDAREA names are specified. For details about what characters are allowed in RDAREA names in value-specification, see 1.1.7 Specification of names. Note also that leading and trailing whitespace is ignored in RDAREA names specified in value-specification. If the RDAREA name is enclosed in double quotation marks ("), only whitespace outside the double quotation marks is ignored.
- If cursor-name or extended-cursor-name is specified, specify the same set of RDAREAs (in any order) as the RDAREAs specified in the cursor declared in the cursor declaration. An error occurs if they are not specified.
- If specifying an RDAREA that uses the inner replica facility, specify the original RDAREA name. To target the replica RDAREA, use the change current database command (pddbchg command), or the PDDBACCS operand in the client environment definition, to switch the RDAREA to access to the replica RDAREA.
Specify this operand when a correlation name is to be used for the table being updated.
The scope of correlation-name is the entire UPDATE statement. The table identifier to be updated has no scope.
- SQL-optimization-specification-on-used-index
For details about SQL optimization specifications on used indexes, see 2.24 SQL optimization specification.
- SET update-object = update-value
Specifies a table identifier when the value of a column or the value of an attribute of an abstract data type is to be updated.
- column-name
- Specifies the name of a column to be updated.
- component-specification
- Specifies the attribute of the abstract data type being updated.
- column-name [{subscript|*}]
- column-name
- Specifies a repetition column whose elements are to be updated.
- [{subscript|*}]
- Specifies the position of the element that is to be updated; to update the last element, specify an asterisk (*).
- If there are no elements in the repetition column that is to be updated, specifying an asterisk is meaningless.
- update-value
- Specifies any of the following items to be used as the paired column's new value:
- Column name
- Component specification
- Literal
- Value expression (including an arithmetic or concatenation operation)
- Scalar subquery
- USER
- CURRENT_DATE
- CURRENT_TIME
- CURRENT_TIMESTAMP[(p)]
- NULL (null value)
- DEFAULT (Represents the predefined value for the column to be updated.)
- Embedded variable (and indicator variables)
- ? parameter
- SQL variables or SQL parameters
- ARRAY [element-value [, element-value] ...]#
#: None of the following can be specified as the value of an element:
A column other than a repetition column
A subscripted repetition column
A literal
A value expression (including arithmetic and concatenation operations)
A scalar subquery
USER
CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP[(p)]
NULL (represents the null value)
DEFAULT
Embedded variables (and indicator variables)
? parameter
An SQL variable or SQL parameter
- Rules on update values
- When a column name is specified as the updated value, the name of a column with either the same data type as the column or attribute being updated or a data type that can be converted to that data type must be specified.
- When a scalar subquery is specified as the updated value, the data type of the column obtained as a result of the scalar subquery must be the same as that of the column or attribute to be updated or a data type that can be converted to it.
- If the data to be updated is character data, it must use the same character set as the updated value. However, if an embedded variable (that uses the default character set), ? parameter, or string constant is specified as the updated value, it will automatically be converted to the character set used for the data being updated.
- When an unsubscripted repetition column is specified as the update object, the name of an unsubscripted repetition column must be specified as the column name of the update value or in the selection expression for the scalar subquery.
- When a subscripted repetition column is specified as the update object, the name of an unsubscripted repetition column cannot be specified as the column name of the update value or in the selection expression for the scalar subquery.
- Embedded variables and indicator variables cannot be specified in UPDATE statements or procedures that are preprocessed by the PREPARE statement. In a procedure, either an SQL variable or an SQL parameter must be used.
- When an embedded variable (indicator variable), ? parameter, SQL variable, or SQL parameter is specified, the data type of the embedded variable (for a ? parameter, the embedded variable that is specified to assign a value to it), SQL variable, or SQL parameter should be the data type of the column or attribute being updated or a data type that can be converted to that data type.
If the column to be updated is a repetition column, the embedded variable (indicator variable) or ? parameter that holds the update value should have a repetition structure.
- When an indicator variable with a negative value is specified, the value of the embedded variable is interpreted to be the null value and the null value is set in the corresponding column. When the value of a specified indicator variable is 0 or positive, the value of the embedded variable is assigned to the corresponding column.
A ? parameter can be specified only in an UPDATE statement that is preprocessed by the PREPARE statement.
- The value to be assigned to the ? parameter is specified in an embedded variable in the USING clause of the EXECUTE statement that corresponds to the PREPARE statement that prepares the UPDATE statement.
- The update value must have a data type that can be converted to or compared with the column to be updated.
However, if the column to be updated is of the national character data type and a character string literal is specified as the update value, the character string literal will be treated as a national character string literal. When a character string literal is treated as a national character string literal, only the character data length is checked; the character codes are not checked.
- When the column to be updated is an unsubscripted repetition column, none of the following can be specified as the update value: a literal, value expression, USER, CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP[(p)], SQL variable, or SQL parameter.
- ARRAY [element-value [, element-value] ...] can be specified only if the column being updated is a repetition column.
- A maximum of 30,000 element values can be specified in ARRAY [element-value [,element-value] ...], provided that the number does not exceed the maximum permissible number of elements for the column being updated.
- DEFAULT, specified as an update value, takes the following predefined values:
If the DEFAULT clause is specified for the column to be updated, a specified predefined value takes effect.
If DEFAULT is not specified but WITH DEFAULT is specified, the predefined value for WITH DEFAULT takes effect.
If neither the DEFAULT clause nor WITH DEFAULT is specified, NULL becomes the predefined value.
- Rules on element values
- Embedded variables and indicator variables cannot be specified in an UPDATE statement preprocessed by a PREPARE statement or in a procedure. An SQL variable or parameter should be used in a procedure.
- When an embedded variable (indicator variable), ? parameter, SQL variable, or SQL parameter is specified, the data type of the embedded variable (for a ? parameter, the embedded variable that is specified to assign a value to it), SQL variable, or SQL parameter should be the data type of the column or attribute being updated or a data type that can be converted to that data type. In addition, the embedded variable (indicator variable) or ? parameter should have a simple structure.
- If an indicator variable is specified and its value is negative, the value of the embedded variable is interpreted to be NULL, and the NULL value is assigned to the corresponding column. If the value of the indicator variable is either 0 or positive, the value of the embedded variable will be assigned to the corresponding column.
- The ? parameter can be specified only in an UPDATE statement that is preprocessed by a PREPARE statement. The value to be assigned to the ? parameter is specified in an embedded variable in a USING clause in the EXECUTE statement for the PREPARE statement.
- The value of an element should be of that can be converted into or compared with the data type of the column to be updated. However, if the column to be updated is of the national character data type and a character string constant is specified as the update value, the character string constant is treated as a national character string constant, in which case its character codes are not checked and only the length of the character data is checked.
- When a scalar subquery is specified as an element value, an unsubscripted repetition column cannot be specified in the selection expression for the scalar subquery.
- If DEFAULT is specified as the value of an element, the predefined value is the null value.
- Rules on updating a column of the BLOB type or the BINARY type with a defined length of 32,001 bytes or greater, using concatenation operations
- A column specification can be specified in the first operand of the concatenation operation. In the second term, an embedded variable, a ? parameter, an SQL variable, and an SQL parameter can be specified.
- When specifying a concatenation operation of the BLOB type or the BINARY type with a defined length of 32,001 bytes or greater as an update value, be sure to specify the same column as the target of updating in the first operand of the concatenation operation.
- A concatenation operation cannot be specified as the result of a concatenation operation.
- The only data type that can be concatenated with a BLOB type is another BLOB type. Numeric data, character data, national character data, or mixed character data cannot be concatenated.
- The only data type that can be concatenated with the BINARY type is another BINARY type. Numeric data, character data, national character data, or mixed character data cannot be concatenated.
- The results of a concatenation operation allow the null value, irrespective of whether the value of the first or second operand is subject to NOT NULL constraints.
- A concatenation operation producing the actual length of the result exceeding the maximum length for the BLOB type or the BINARY type (2,147,483,647 bytes) results in an error.
- If any of the following definitions is in the table to be updated, an update using concatenation operations can cause an error:
An UPDATE trigger is defined for the table to be updated
The column to be updated using concatenation operations is specified in a search condition with a check constraint.
- SET (update-object,update-object[,update-object] ...) = row-subquery
Specifies updating objects in order to update the values of multiple columns with the results of a row subquery. At least two update objects must be specified.
- column-name
- Specifies the name of a column that is to be updated.
- component-specification
- Specifies the attribute of an abstract data type that is to be updated.
- column-name [{subscript|*}]
- column-name
- Specifies a repetition column whose elements are to be updated.
- [{subscript|*}]
- Specifies the position of the element that is to be updated; to update the last element, specify an asterisk (*).
- If there are no elements in the repetition column that is to be updated, specifying an asterisk is meaningless.
- row-subquery
- Specifies a row subquery for retrieving the data to be updated. For details about row subqueries, see 2.4 Subqueries.
- The following rules apply to row subqueries:
- The number of updates and the number of selection expressions in the row subquery should be the same.
- The data type of the column to be obtained as the result of a row subquery should be the same as the data type of the column or attribute to be updated, or it must be an equivalent convertible data type.
- If the data to be updated is character data, it must use the same character set as the column obtained from the row subquery.
- When an unsubscripted repetition column is specified as an object of the update, specify the column name of an unsubscripted repetition column for the column in the selection expression in the row subquery.
- When a subscripted repetition column is specified as an object of the update, do not specify the column name of an unsubscripted repetition column for the column in the selection expression in the row subquery.
- Rules for the SET clause
- When elements of a repetition column are being updated by specifying a subscript in the SET clause, only one update can be specified in the same SET clause relative to the same element in the same column.
- When a repetition column is updated without specifying a subscript in the SET clause, the repetition column cannot be updated by specifying a subscript in the same SET clause.
- When a repetition column is updated by specifying the asterisk (*) as a subscript, the repetition column cannot be updated in the same SET clause.
- ADD repetition-column-name [{subscript |*}] = {ARRAY [element-value [, element-value] ...] | ?-parameter |: embedded-variable [: indicator-variable]}
Specifies addition of one or more elements to a repetition column.
- repetition-column-name [{subscript |*}]
- repetition-column-name
- Specifies the repetition column to which elements are to be added.
- [{subscript | *}]
- Specifies as the subscript the position at which the elements are to be added. The asterisk (*) is specified when the added elements will be the last elements in the column.
- ARRAY [element-value [, element-value] ...]
- Specifies the element values that are to be added. The following items can be specified as element values:
- Column names (other than the names of repetition columns)
- Subscripted repetition columns
- Literals
- Value expressions (including arithmetic and concatenation operations)
- Scalar subquery
- USER
- CURRENT_DATE
- CURRENT_TIME
- CURRENT_TIMESTAMP[(p)]
- NULL (representing the C value)
- DEFAULT
- Embedded variables (and indicator variables)
- ? parameters
- SQL variables or parameters
- For rules governing the value of an element, see Rules on element values for SET update-object = update-value.
- ?-parameter
- The data type of a ? parameter should be the data type of the column being updated or a data type that can be converted to that data type. The structure of the ? parameter should be the repetition structure.
- A ? parameter can be specified only in an UPDATE statement that is preprocessed by the PREPARE statement. The value to be assigned to the ? parameter can be specified in the embedded variable in a USING clause in the EXECUTE statement for the PREPARE statement that performed preprocessing.
- :embedded-variable [: indicator-variable]
- Embedded variables and indicator variables cannot be specified in an UPDATE statement preprocessed by the PREPARE statement or in a procedure.
- The data type of an embedded variable (indicator variable) should be the data type of the column to be updated or a data type that can be converted to that data type. The structure of the embedded variable (indicator variable) should be the repetition structure.
- If an indicator variable is specified and its value is negative, the value of the embedded variable is interpreted to be the null value, and the null value is assigned to the corresponding column. If the value of the indicator variable is either 0 or positive, the value of the embedded variable will be assigned to the corresponding column.
- Rules for the ADD clause
- Only one addition of elements to a column can be specified in one ADD clause.
- When elements are added, it is important to ensure that the total number of elements in the column after the addition does not exceed the maximum permissible number of elements for the column.
- A maximum of 30,000 values can be specified as element values in ARRAY [element-value [, element-value] ...].
- Elements that are located following elements added by specifying a subscript are moved back. If a number greater by at least 2 than the number of elements in the repetition column to which elements are added is specified, null values are added until the number of elements in the repetition column is (specified elements - 1), and the new element values are added at the trailing end.
- If a multi-column index is defined using the column to which elements are added, the same number of elements must be added to each of the repetition columns comprising the index and at the same element positions using a single ADD clause.
- When an element that uses a cursor is added by specifying CURRENT OF cursor-name in the WHERE clause, column names or value expressions cannot be specified as element values in the ADD clause.
- If DEFAULT is specified as the value of an element, the predefined value is the null value.
- DELETE repetition-column-name [{subscript | *}]
Specifies deletion of elements from a repetition column.
- repetition-column-name [{subscript | *}]
- repetition-column-name
- Specifies the repetition column from which an element is to be deleted.
- [{subscript | *}]
- Specifies as the subscript the position of the element that is to be deleted. The asterisk (*) is specified when the last element in the column is to be deleted.
- Rules for the DELETE clause
- When a subscript is specified in the DELETE clause, only one column element can be specified for deletion per DELETE clause.
- The subscript value cannot be greater than the number of elements in the column.
- When the asterisk is specified, no other elements in the column can be specified for deletion simultaneously in the same DELETE clause.
- Elements that follow a deleted element move up in order.
- If a multi-column index is defined using the column from which elements are deleted, the same number of elements must be deleted from each of the repetition columns comprising the index at the same element positions using a single DELETE clause.
- [WHERE {search-condition|CURRENT OF cursor-name|extended-cursor-name}]
When this clause is omitted, all rows in the specified table are updated.
- search-condition
- Specifies criteria for selecting the rows to be updated.
- The UPDATE statement updates all rows that satisfy the specified search condition.
- cursor-name
- Specifies the name of the cursor to be used to indicate the row to be updated.
- A cursor name cannot be specified if the UPDATE statement is preprocessed by the PREPARE statement.
- The cursor specified in cursor-name should be one that was declared in the cursor declaration. The name of the column to be updated using the UPDATE statement must be specified in the FOR UPDATE clause of the cursor declaration.
- When a cursor name is specified, the cursor must be specified as updatable in the cursor declaration. For details about updatable cursors, see item 4 under Common rules in DECLARE CURSOR Format 1 (Declare cursor).
- The specified cursor must be opened by the OPEN statement and positioned by the FETCH statement at the row to be updated.
- The position of the specified cursor remains unchanged after the UPDATE statement has executed. To update a row that follows the updated row, the cursor must be moved by executing a FETCH statement for the cursor.
- extended-cursor-name
- Specifies an extended cursor name that identifies the cursor that points to the row to be updated.
- An extended cursor name cannot be specified if it is preprocessed by the PREPARE statement.
- The extended cursor name that identifies the cursor allocated by the ALLOCATE CURSOR statement should be specified. However, a result set cursor cannot be specified.
- If extended-cursor-name is specified, an extended cursor must be specified for queries in which the FOR UPDATE clause is specified. For details about the FOR UPDATE clause, see the FOR UPDATE clause under Operands in Dynamic SELECT statement Format 1 (Retrieve dynamically) in this chapter.
- The cursor identified by the extended cursor name must be open and also must be positioned on the row to be updated.
- The position of the cursor identified by extended-cursor-name remains unchanged after the execution of the UPDATE statement. If a row after the updated row is to be updated, the FETCH statement should be executed on the cursor to move it.
- For extended cursor names, see 2.27 Extended cursor name.
Specifies that if the table to be updated is being used by another user, the transaction is to be cancelled and invalidated.
When the WITH ROLLBACK option is omitted and the table to be updated is being used by another user, the system executes the UPDATE statement after the transaction issued by the other user has terminated.
Common rules
- When a column of the INTEGER or SMALLINT type is updated with fixed-point or floating-point data, any decimal places (to the right of the decimal point) are truncated before the update action. Similarly, when a column of the DECIMAL type is updated, any digits below the scaling for the column are truncated before the update action.
- Character data, BLOB data, or BINARY data with a length greater than or equal to the length that was determined when the table was defined cannot be input as a value of the column to be updated.
- Numeric data outside the defined range cannot be input as an update column value.
- If the data that updates a fixed-length character string column (including a national character string or a mixed character string) is shorter than the length of the column, the data is inserted left-justified and the column is filled with trailing blanks.
- When updating a column or attribute of the BLOB data type, as an update value you can specify a column specification, a component specification, an embedded variable, a ? parameter, an SQL variable, an SQL parameter, a concatenation operation, the SUBSTR scalar variable, a function call, a subquery, or NULL.
- When a BLOB data type column or an attribute is updated, HiRDB deletes the existing data after writing the new data into the database. Therefore, the LOB RDAREA for data updating requires sufficient free space for writing the new data. An RDAREA full error may result if sufficient free space cannot be allocated.
- When a column or an attribute of an abstract data type is updated, an embedded variable or a ? parameter cannot be specified as the update value.
- When a column or an attribute of an abstract data type is updated, an abstract data type value cannot be specified as the update value, including a BLOB attribute for which no LOB attributes storage RDAREA name was specified at the time of table definition.
- When elements are updated in or deleted from a column and a subscript is specified that is greater than the current number of elements in the column, or the asterisk is specified when the number of elements is 0, there will be no elements to be updated or deleted, in which case the specified updating or deletion action for the column is ignored. In such a case, W is set in the SQLWARN7 variable of the SQLCA.
- Only one SET, ADD, or DELETE clause can be specified per SQL statement.
- A maximum of 30,000 items can be specified in each SET, ADD, or DELETE clause.
- SET, ADD, and DELETE clauses are executed from left to right in the specified order.
- If the user LOB RDAREA that stores a LOB column or LOB attribute is in the frozen update status, the LOB column or LOB attribute cannot be updated (an attempt to update it causes an already frozen error).
- The UPDATE statement cannot be executed on falsification prevented tables. However, updatable columns can be updated.
The following table indicates the updatability of column values when UPDATE ONLY FROM NULL is specified for a falsification-prevented table:
Column value before update | Column value after update | Updatability |
---|
Null value | Null value | Y |
Null value | Non-null value | Y |
Non-null value | Null value | N |
Non-null value | Non-null value# | N |
- Legend:
- Y: Updatable.
- N: Not updatable.
- Note
- For a repetition column, only updating by column from the null value (a value for which the number of elements is 0) without a subscript specification.
- #: Includes the same value as the pre-update value.
- If the index constituent column of a table with a WITHOUT ROLLBACK specification is the update object column, updating cannot be executed if the update value for the index constituent column is other than the pre-update value. For details, see the rules on WITHOUT ROLLBACK in CREATE TABLE (Define table) in Chapter 3.
- If the table to be updated is a shared table and if an index is defined for the column to be updated, the LOCK statement should be executed in the lock mode on the shared table before updating. An attempt to update without executing the LOCK statement the value of a column on which a shared table is defined can cause an error. However, the LOCK statement need not be executed if the value of the column for which an index is defined is not changed. For details about updating a shared table, see the HiRDB Version 9 Installation and Design Guide. For objects of locking for the execution of the LOCK statement on a shared table, see the notes in LOCK statement (Lock control on tables).
- If the table to be updated meets all of the following conditions and if columns in the table are to be updated by DEFAULT, the LOCK statement in the locking mode should be executed on the shared table before updating; an attempt to update the table without executing the LOCK statement can cause an error:
- The table to be updated is a shared table.
- The column to be updated is of the timestamp data type.
- When the table was defined, CURRENT_TIMESTAMP USING BES was specified as the default.
- If a cursor name or an extended cursor name is specified in search-condition, a subquery in which the table to be updated is specified in a FROM clause cannot be specified in the SET clause.
- If RDAREA-name-specification is specified, you cannot use an index in which the number of partitions is different from the number of partitions of the table. When defining an index for queries that specify RDAREA-name-specification, define an index that has the same number of partitions as the number of partitions of the table.
Rules on referential constraints
- For the rules on updating the primary key of a referenced table and the foreign key of a referencing table, see the explanation of referencing actions in CREATE TABLE (Define table) in Chapter 3.
- For the updating of values of foreign key constituent columns in a referencing table, the referencing table is searched to determine whether the updating value is included in the value of the primary key constituent column in the referenced table. The data guarantee level during the search through the referenced table assumes the share mode. For this reason, if during the updating of the referencing table, operations are performed on the referenced table by another transaction, the updating action goes into a wait state until the transaction is settled.
- For the deletion of a rows in a referenced table for which constraint operations are defined in RESTRICT, the referencing table is searched to determine whether the value of the primary key constituent column in the rows to be deleted is included in the value of a foreign key constituent column in the referencing table. The data guarantee level during the search through the referencing table assumes the share mode. For this reason, if, during the deletion of rows in the referenced table for which constraint operations are defined in RESTRICT, operations are performed on the referencing table by another transaction, the row deletion action goes into a wait state until the transaction is settled.
- If any combination of the conditions listed below occurs, data incompatibility can occur between the referenced table and the referencing table subject to referential constraints. Such incompatibility can also occur regardless of whether the constraint operation is RESTRICT or CASCADE. For rules on referential constraints, see the HiRDB Version 9 Installation and Design Guide.
- The transaction that deletes rows in the referencing table is different from the transaction that updates or deletes rows in the referenced table.
- The above two transactions are executed simultaneously.
- The value of the primary key constituent column deleted from the referencing table is the same as the value of the foreign key constituent column that is either updated in or deleted from the referenced table.
- Either the transaction that deletes rows from the referencing table is committed or the transaction that updates or deletes rows in the referenced table is rolled back.
Note
A cursor name, similar to an embedded variable name, is effective only within a compile-unit module. Multiple SQLs relative to the same cursor cannot be used in multiple modules.
Examples
- In a stock table named STOCK, change to 100 the stock quantity (SQTY) column for any row whose product code (PCODE) column is 302S:
UPDATE STOCK
SET SQTY = 100
WHERE PCODE = '302S'
- In the product code (PCODE) column in the stock table (STOCK), apply a 20% discount to the unit prices (PRICE) of products that end with the letter 'S':
UPDATE STOCK
SET PRICE = PRICE*0.8
WHERE PCODE LIKE'%S'
- Update the unit price (PRICE) and stock quantity (SQTY) columns in the stock table (STOCK) using values that have been read into embedded variables:
UPDATE STOCK
SET PRICE=:XPRICE,SQTY=:XSQTY
- Find the product whose product code (PCODE) column value in the STOCK table is 302S and change its stock quantity (SQTY) to the stock quantity (XQTY) of the product whose product code (PCODE) column value is 302S in stock table 2 (STOCK2); the STOCK2 table has the same column definition as the STOCK table:
UPDATE STOCK
SET SQTY=
(SELECT SQTY FROM STOCK2 WHERE PCODE='302S')
WHERE PCODE='302S'
- Change the stock quantity (SQTY) and the unit price (PRICE) of the product whose product code (PCODE) column value in the STOCK table is 302S to the stock quantity (XQTY) and unit price (PRICE) of the product whose product code (PCODE) column value is 302S in stock table 2 (STOCK2); the STOCK2 table has the same column definition as the STOCK table:
UPDATE STOCK
SET (PRICE,SQTY)=
(SELECT PRICE,SQTY FROM STOCK2 WHERE PCODE='302S')
WHERE PCODE='302S'