AntDB-Oracle Compatibility Developer’s Manual P3–28
Published: 2023-09-06 08:48:43

##### UPDATE

**Name**

UPDATE -- update the records of the table

**Summary**

UPDATE table

SET column = { expression \| DEFAULT } \[, ...]

\[ WHERE condition ]

\[ RETURNING return\_expression \[, ...]

{ INTO { record \| variable \[, ...] }

\| BULK COLLECT INTO collection \[, ...] } ]

**Description**

The UPDATE command changes the value of the specified column in all rows that meet the condition. We only need the SET clause to show the columns whose values are to be changed, while the columns that are not modified remain at their original values.

RETURNING INTO { record \| variable \[, ...] The } clause can only be used in SPL programs. If the result set is empty, it sets the target variable or the contents of the variable to null.

The user must have UPDATE permission on the table to update the record, and the associated SELECT permission is also required if the value needs to be read from any table for an expression or condition.

**argument**

table

The name of the data table to update. (can be referenced in a schema qualified manner)

column

The name of a column in the table.

expression

The expression assigned to the column. The expression can be the original value of a column in the table and the value of another column.

DEFAULT

Sets the column to its default value. (If no expression is specified, then null values will be assigned)

condition

An expression that returns a BOOLEAN type value. Only records that match the return value of the expression is true can be updated.

return\_expression

An expression containing one or more columns of a table. If the table column name is specified in the return\_expression, then when the return\_expression is calculated, the value of the substitute column is determined in the following manner.

If a value is assigned to the column specified in return\_expression in the UPDATE command, then the assigned value is used when calculating return\_expression.

If the UPDATE command does not assign a value to the column specified in return\_expression, then the current value of the column in the operated record is the result of the return\_expression calculation.

record

The field value of the record is the calculated return\_expression. The first return\_expression is assigned to the first field in the record, the second return\_expression is assigned to the second field in the record... The assignment is done in this order. The number of fields in the record must match the number of expressions, and the data type of the fields must be compatible with the type of the assigned expressions.

variable

The computed expression is assigned to a variable. If multiple return\_expressions and variables are specified, the assignment is made in the order that the first return\_expression is assigned to the first variable and the second return\_expression is assigned to the second variable in the record. The number of variables specified after the keyword must be exactly the same as the number of expressions following the keyword RETURNING, and the variables must be compatible in type with the assigned expressions.

collection

The members of a collection are created from the calculated expressions. Here it can be either a single collection (either a collection of individual fields or a collection of record types) or multiple collections consisting of individual fields. The returned expressions must match the number, and order of occurrence, of all fields in the specified set. Each corresponding return\_expression and collection field must be type-compatible.

**Example**

In table dept, change the geographic location of department number 20 to AUSTIN.

```
UPDATE dept SET loc = 'AUSTIN' WHERE deptno = 20; 
```

Increase the salary of all employees in table emp whose job is SALESMAN by ten percent and increase the commission by $500.

```
UPDATE emp SET sal = sal * 1.1, comm = comm + 500 WHERE job = 'SALESMAN'; 
```

Increase the salary of a particular employee by $1,000 and print the information about this employee.

```
\set PLSQL_MODE ON
CREATE OR REPLACE FUNCTION update_sal (eno in int) return int
IS
type t_table is table of emp;
var_sal t_table;
BEGIN
update emp set sal = sal + 1000  where empno = eno returning * bulk collect into var_sal;

for i in 1..var_sal.count loop
 DBMS_OUTPUT.PUT_LINE(var_sal[i].empno); 
	DBMS_OUTPUT.PUT_LINE(var_sal[i].ENAME); 
	DBMS_OUTPUT.PUT_LINE(var_sal[i].JOB);
	DBMS_OUTPUT.PUT_LINE(var_sal[i].HIREDATE);
end loop;
return var_sal.count;
END;
/
\set PLSQL_MODE OFF
select update_sal(7876);
NOTICE:  7876
NOTICE:  ADAMS
NOTICE:  CLERK
NOTICE:  1987-05-23 00:00:00
 UPDATE_SAL 
------------
          1
(1 row)
```