UPDATE 和 DELETE 语句中的子查询

您可以在 UPDATE 和 DELETE 语句中嵌套子查询。

UPDATE 子查询

您可以通过在 UPDATE 语句中嵌套子查询，根据其他表中的值更新一个表中的记录。下面的示例通过几个非相关子查询说明了这一点。您可以使用下表重现此示例：

=> CREATE TABLE addresses(cust_id INTEGER, address VARCHAR(2000));
CREATE TABLE
dbadmin=> INSERT INTO addresses VALUES(20,'Lincoln Street'),(30,'Booth Hill Road'),(30,'Beach Avenue'),(40,'Mt. Vernon Street'),(50,'Hillside Avenue');
 OUTPUT
--------
      5
(1 row)

=> CREATE TABLE new_addresses(new_cust_id integer, new_address Boolean DEFAULT 'T');
CREATE TABLE
dbadmin=> INSERT INTO new_addresses VALUES (20),(30),(80);
 OUTPUT
--------
      3
(1 row)

=> INSERT INTO new_addresses VALUES (60,'F');
 OUTPUT
--------
      1

=> COMMIT;
COMMIT

对这些表的查询会返回以下结果：

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

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

以下 UPDATE 语句使用非相关子查询来联接 customer ID 上的 new_addresses 和 addresses 记录。UPDATE 在联接的 addresses 记录中设置 'New Address' 值。语句输出表明更新了三行：
```
=> 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)
```

查询 addresses 表，以查看匹配客户 ID 20 和 30 的更改。客户 ID 40 和 50 的地址未更新：

=> SELECT * FROM addresses;
 cust_id |      address
---------+-------------------
      40 | Mt. Vernon Street
      50 | Hillside Avenue
      20 | New Address
      30 | New Address
      30 | New Address
(5 rows)

=>COMMIT;
COMMIT

DELETE 子查询

您可以通过在 DELETE 语句中嵌套子查询，根据其他表中的值删除一个表中的记录。

例如，您想从之前用于更新 addresses 中的记录的 new_addresses 中移除记录。以下 DELETE 语句使用非相关子查询来联接 customer ID 上的 new_addresses 和 addresses 记录。然后它从表 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

查询 new_addresses 以确认记录已被删除：

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