GREENPLUM: UPDATE SYNTAX EXAMPLES

　

Change 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 = '2006-07-03';

Use the alternative column-list syntax to do the same update:

UPDATE weather SET (temp_lo, temp_hi, prcp) = (temp_lo+1, temp_lo+15, DEFAULT)

WHERE city = 'San Francisco' AND date = '2006-07-03';

Increment the sales count of the salesperson who manages the account for Acme Corporation, using the

FROM clause syntax (assuming both tables being joined are distributed in Greenplum Database on the id column):

UPDATE employees SET sales_count = sales_count + 1 FROM accounts

WHERE accounts.name = 'Acme Corporation'

AND employees.id = accounts.id;

Perform the same operation, using a sub-select in the

WHERE clause:

UPDATE employees SET sales_count = sales_count + 1 WHERE id =

(SELECT id FROM accounts WHERE name = 'Acme Corporation');

Attempt to insert a new stock item along with the quantity of stock. If the item already exists, instead update the stock count of the existing item. To do this without failing the entire transaction, use savepoints.

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;

　

According to the standard, the column-list syntax should allow a list of columns to be assigned from a single row-valued expression, such as a sub-select:

UPDATE accounts SET (contact_last_name, contact_first_name) =

(SELECT last_name, first_name FROM salesmen

WHERE salesmen.id = accounts.sales_id);

This is not currently implemented — the source must be a list of independent expressions.

Some other database systems offer a FROM option in which the target table is supposed to be listed again within FROM. That is not how Greenplum Database interprets FROM. Be careful when porting applications that use this extension.