UPDATE

Update the rows of the table.

Summary

[ WITH [ RECURSIVE ] with_query [, ...] ]
                UPDATE [ONLY] table [[AS] alias]
                   SET {column = {expression | DEFAULT} |
                   (column [, ...]) = ({expression | DEFAULT} [, ...])} [, ...]
                   [FROM fromlist]
                   [WHERE condition| WHERE CURRENT OF cursor_name ]

describe

UPDATE changes the value of the specified column in all rows that meet the criteria. Simply mention the column you want to modify in the SET clause; a column that is not explicitly modified will retain its previous value.

By default, UPDATE will update rows in the specified table and all its subtables. If you only want to update the specific table mentioned, you must use the ONLY clause.

There are two ways to modify a table using the information contained in other tables in the database: use subselect, or specify another table in the FROM clause. Which technology is more suitable depends on the specific situation.

If the WHERE CURRENT OF clause is specified, the updated row is the latest row obtained from the specified cursor.

The WHERE CURRENT OF clause is not supported in the copy table.

You must have UPDATE privileges on the table or at least on the columns you want to update. You must also have SELECT privileges to read any columns in expression or condition.

Note: By default, the YMatrix database acquires the EXCLUSIVE lock of the table for the UPDATE operation of the heap table. After the global deadlock detector is enabled, the lock mode of the UPDATE operation on the heap table is ROW EXCLUSIVE.

Output

After successful completion, the UPDATE command will return the command mark in the following format:

UPDATE count

where count is the number of updated rows. If count is 0, there are no rows that meet the criteria (this is not considered an error).

Parameters

with_query

The WITH clause allows you to specify one or more subqueries that can be referenced by name in a UPDATE query.
For UPDATE commands containing WITH clauses, the clause can only contain SELECT commands, while the WITH clause cannot contain data modification commands (INSERT, UPDATE or DELETE).
Query (SELECT statement) may also contain a WITH clause. In this case, two groups of with_query can be referenced in the UPDATE query, but the second group takes precedence because it is nested tighter.

ONLY

If specified, only rows in the specified table are updated. If not specified, any tables inherited from the specified table are also processed.

table

The name of the existing table (can be modified with schema).

alias

Alternative name for the target table. Once an alias is provided, it will completely hide the actual name of the table. For example, given UPDATE foo AS f, the rest of the UPDATE statement must refer to this table as f instead of foo.

column

Name of the column in the table. If desired, you can use subfield names or array subscripts to qualify column names. Do not include the table name in the specification of the target column.

Expression

The expression assigned to the column. This expression can use the old values of this and other columns in the table.

DEFAULT

Set the column to its default value (NULL if no specific default expression is assigned).

fromlist

List of table expressions that allow columns in other tables to appear in WHERE conditions and update expressions. This is similar to a list of tables that can be specified in the FROM clause of a SELECT statement. Note that the target table must not appear in the fromlist unless you intend to do a self-join (in which case the target table must appear in the fromlist with an alias).

condition

This expression returns a value of type boolean. Only rows that return true will be updated.

cursor_name

Cursor name used in the WHERE CURRENT OF condition. The row to be updated is the row that is most recently obtained from the cursor. The cursor must be a non-grouping query on the UPDATE command target table. For more information on creating cursors, see DECLARE.
WHERE CURRENT OF cannot be specified with the Boolean condition. Note that WHERE CURRENT OF cannot be specified with the Boolean condition. UPDATE...WHERE CURRENT OF statement can only be executed on the server, for example in an interactive psql session or script. Language extensions (such as PL/pgSQL) do not support updating cursors.

output_expression

After each line is updated, it is calculated by the UPDATE command and returns the expression. This expression can use any column name of one or more tables listed in the FROM. Enter * to return all columns.

output_name

The name of the column used to return.

Note

SET is not allowed on the YMatrix distribution key column of the table.

When an FROM clause exists, it is essentially connecting the target table to the table mentioned in the from list, and each output row of the join represents the update operation of the target table. When using FROM, make sure that the connection produces at most one output line for each line to be modified. In other words, the target row should not be joined to a row in another table. If so, then only one of the join rows will be used to update the target row, but it will be difficult to predict which row will be used.

Because of this uncertainty, it is safer to refer to other tables only within the subselect, although it is generally harder to read and slower than using a join.

Direct execution of UPDATE and DELETE commands on specific partitions (subtables) of partitioned tables is not supported. Instead, execute these commands on the root partition table (the table created using the CREATE TABLE command).

Example

Change the kind column in table films from Drama to Dramatic:

UPDATE films SET kind = 'Dramatic' WHERE kind = 'Drama';

Adjust the temperature entry and reset the precipitation in a row of the table weather to the default value:

UPDATE weather SET temp_lo = temp_lo+1, temp_hi =
                temp_lo+15, prcp = DEFAULT
                WHERE city = 'San Francisco' AND date = '2016-07-03';

Use the alternative column list syntax to make the same update:

UPDATE weather SET (temp_lo, temp_hi, prcp) = (temp_lo+1,
                temp_lo+15, DEFAULT)
                WHERE city = 'San Francisco' AND date = '2016-07-03';

Use the FROM clause syntax to increase the sales number of salespeople who manage Acme Corporation accounts (assuming that both connected tables are distributed based on id columns in the YMatrix database):

UPDATE employees SET sales_count = sales_count + 1 FROM
                accounts
                WHERE accounts.name = 'Acme Corporation'
                AND employees.id = accounts.id;

Use subselects in the WHERE clause to perform the same operation:

UPDATE employees SET sales_count = sales_count + 1 WHERE id =
                (SELECT id FROM accounts WHERE name = 'Acme Corporation');

Try inserting new stock items and stock quantity. If the material already exists, update the inventory quantity of existing materials. To do this without failing the entire transaction, use a savepoint.

BEGIN;
-- Other operations
SAVEPOINT sp1;
INSERT INTO wines VALUES('Chateau Lafite 2003', '24');
-- Assume the above fails because of a unique key violation,
-- so now we issue these commands:
ROLLBACK TO sp1;
UPDATE wines SET stock = stock + 24 WHERE winename = 'Chateau
Lafite 2003';
-- Continue with other operations, and eventually
COMMIT;

compatibility

This command complies with SQL standards, but the FROM clause is a YMatrix database extension.

According to the criteria, the column list syntax should allow the allocation of column lists from a single row value expression (such as sub-select):

UPDATE accounts SET (contact_last_name, contact_first_name) =
                (SELECT last_name, first_name FROM salesmen
                WHERE salesmen.id = accounts.sales_id);

Not currently implemented - the source must be a list of independent expressions.

Some other database systems provide FROM options, and the target table should be listed again in the FROM. That's not how the YMatrix database interprets FROMs. Be careful when migrating applications with this extension.