 | Documentation |
|---|---|
UPDATESynopsisUPDATE [ ONLY ] table SET column = { expression | DEFAULT } [, ...]
[ FROM fromlist ]
[ WHERE condition ]
UPDATE [ ONLY ] table SET column = { expression | DEFAULT } [, ...]
[ FROM fromlist ]
[ WHERE condition ]
[ RETURNING { * | return_expression [, ...] }
[ INTO { record | variable [, ...] } ] ]
DescriptionUPDATE changes the values of the specified columns in all rows that satisfy the condition. Only the columns to be modified need be mentioned in the SET clause; columns not explicitly modified retain their previous values. By default, UPDATE will update rows in the specified table and all its subtables. If you wish to only update the specific table mentioned, you must use the ONLY clause. There are two ways to modify a table using information contained in other tables in the database: using sub-selects, or specifying additional tables in the FROM clause. Which technique is more appropriate depends on the specific circumstances. If using RETURNING, the clause, INTO { record | variable [, ...] }, must be specified if UPDATE is used as a program statement in a procedure, function, package, trigger, or anonymous block. INTO { record | variable [, ...] } must be omitted if the command is not given within the context of a program - for example as a stand-alone query in PSQL or SQL Interactive. If RETURNING * is specified, the effect is the same as if RETURNING return_expression [, ...] was given where each return_expression is a column in table, for each column in table, given in the same order as the columns in table. In an SPL program, if the result set of the UPDATE command contains more than one row, then the contents of the target record or variables of the INTO clause are set to the RETURNING clause expressions based on one arbitrary row of the result set. If the result set is empty, then the contents of the target record or variables are set to null. You must have the UPDATE privilege on the table to update it, as well as the SELECT privilege to any table whose values are read in the expressions or condition. Parameters
OutputsOn successful completion, an UPDATE command returns a command tag of the form UPDATE count The count is the number of rows updated. If count is 0, no rows matched the condition (this is not considered an error). ExamplesChange the word Drama to Dramatic in the column kind of the table films: UPDATE films SET kind = 'Dramatic' WHERE kind = 'Drama';
Adjust temperature entries and reset precipitation to its default value in one row of the table weather: UPDATE weather SET temp_lo = temp_lo+1, temp_hi = temp_lo+15, prcp = DEFAULT WHERE city = 'San Francisco' AND date = '2003-07-03';
Increment the sales count of the salesperson who manages the account for Acme Corporation, using the FROM clause syntax: UPDATE employees SET sales_count = sales_count + 1 FROM accounts WHERE accounts.name = 'Acme Corporation' AND employees.id = accounts.sales_person; Perform the same operation, using a sub-select in the WHERE clause: UPDATE employees SET sales_count = sales_count + 1 WHERE id = (SELECT sales_person FROM accounts WHERE name = 'Acme Corporation'); Attempt to insert a new stock item along with the quantity of stock. If the item exists, update the stock count of the existing item. To do this, use savepoints. BEGIN;
SAVEPOINT sp1;
INSERT INTO wines VALUES('Chateau Lafite 2003', '24');
-- Check for unique violation on name
ROLLBACK TO sp1;
UPDATE wines SET stock = stock + 24 WHERE winename='Chateau Lafite 2003';
COMMIT;
|