Vertica Analytics Platform Version 9.2.x Documentation

Subqueries in UPDATE and DELETE Statements

You can nest subqueries within UPDATE and DELETE statements.

UPDATE Subqueries

You can update records in one table according to values in others, by nesting a subquery within an UPDATE statement. The example below illustrates this through a couple of noncorrelated subqueries. You can reproduce this example with the following tables:

CREATE TABLE addresses(cust_id INTEGER, address VARCHAR(2000));
INSERT INTO addresses VALUES(20,'Lincoln Street');
INSERT INTO addresses VALUES(30,'Booth Hill Road');
INSERT INTO addresses VALUES(30,'Beach Avenue');
INSERT INTO addresses VALUES(40,'Mt. Vernon Street');
INSERT INTO addresses VALUES(50,'Hillside Avenue');
				
CREATE TABLE new_addresses(new_cust_id integer, new_address Boolean DEFAULT 'T');
INSERT INTO new_addresses VALUES(20);
INSERT INTO new_addresses VALUES(30);
INSERT INTO new_addresses VALUES(60,'F');
INSERT INTO new_addresses VALUES(80,'T');
COMMIT;

Queries on these tables return the following results:

=> SELECT * FROM addresses;
 cust_id |      address
---------+-------------------
      50 | Hillside Avenue
      30 | Booth Hill Road
      40 | Mt. Vernon Street
      20 | Lincoln Street
      30 | Beach Avenue
(5 rows)

=> SELECT * FROM new_addresses;
 new_cust_id | new_address
-------------+-------------
          30 | t
          20 | t
          80 | t
          60 | f
(4 rows)
  1. The following UPDATE statement uses a noncorrelated subquery to join new_addresses and addresses records on customer IDs. UPDATE sets the value 'New Address' in the joined addresses records. The statement output indicates that three rows were updated:
    => UPDATE addresses SET address='New Address'
       WHERE cust_id IN (SELECT new_cust_id FROM new_addresses WHERE new_address='T');
     OUTPUT
    --------
    3
    (1 row)		
  2. Query the addresses table to see the changes for matching customer ID 20 and 30. Addresses for customer ID 40 and 50 are not updated:
    => SELECT * FROM addresses;
     cust_id |      address
    ---------+-------------------
          20 | New Address
          30 | New Address
          30 | New Address
          40 | Mt. Vernon Street
          50 | Hillside Avenue
    (5 rows)
    
    =>COMMIT;
    COMMIT

DELETE Subqueries

You can delete records in one table based according to values in others by nesting a subquery within a DELETE statement.

For example, you want to remove records from new_addresses that were used earlier to update records in addresses. The following DELETE statement uses a noncorrelated subquery to join new_addresses and addresses records on customer IDs. It then deletes the joined records from table new_addresses:

=> DELETE FROM new_addresses 
    WHERE new_cust_id IN (SELECT cust_id FROM addresses WHERE address='New Address');
 OUTPUT
--------
      2
(1 row)
=> COMMIT;
COMMIT

Querying new_addresses confirms that the records were deleted:

=> SELECT * FROM new_addresses;
 new_cust_id | new_address
-------------+-------------
          60 | f
          80 | t
(2 rows)