mysql - SQL UPDATE 语句中涉及子查询的错误

Question

我正在尝试更新 MySQL 中特定行的字段值，但出现了一个我不太明白的错误。

这里我们有两个表 CUSTOMER_TBL 和 ORDERS_TBL，我想更新 CUSTOMER_TBL 以便 CUST_NAME对于使用 ORD_NUM 等于 23E934 的订单的客户是“DAVIDS MARKET”。

这是两个表:

mysql> DESCRIBE CUSTOMER_TBL;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| CUST_ID      | varchar(10) | NO   | PRI | NULL    |       |
| CUST_NAME    | varchar(30) | NO   |     | NULL    |       |
| CUST_ADDRESS | varchar(20) | NO   |     | NULL    |       |
| CUST_CITY    | varchar(15) | NO   |     | NULL    |       |
| CUST_STATE   | char(2)     | NO   |     | NULL    |       |
| CUST_ZIP     | int(5)      | NO   |     | NULL    |       |
| CUST_PHONE   | char(10)    | YES  |     | NULL    |       |
| CUST_FAX     | varchar(10) | YES  |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
8 rows in set (0.05 sec)

mysql> DESCRIBE ORDERS_TBL;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| ORD_NUM  | varchar(10) | NO   | PRI | NULL    |       |
| CUST_ID  | varchar(10) | NO   |     | NULL    |       |
| PROD_ID  | varchar(10) | NO   |     | NULL    |       |
| QTY      | int(6)      | NO   |     | NULL    |       |
| ORD_DATE | date        | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
5 rows in set (0.07 sec)

以及报错的代码:

mysql> UPDATE CUSTOMER_TBL 
    -> SET CUST_NAME = 'DAVIDS MARKET' 
    -> WHERE CUST_ID = (SELECT C.CUST_ID 
    -> FROM CUSTOMER_TBL C, 
    -> ORDERS_TBL O 
    -> WHERE C.CUST_ID = O.CUST_ID 
    -> AND O.ORD_NUM = '23E934');
ERROR 1093 (HY000): You can't specify target table 'CUSTOMER_TBL' for update in FROM clause

在子查询中引用 CUSTOMER_TBL 有什么问题，我该如何解决这个问题？

谢谢。

Answer 1

你可能会这样做:

UPDATE CUSTOMER_TBL INNER JOIN ORDERS_TBL 
ON CUSTOMER_TBL.CUST_ID = ORDERS_TBL.CUST_ID 
SET CUSTOMER_TBL.CUST_NAME = 'DAVIDS MARKET' 
WHERE ORDERS_TBL.ORD_NUM = '23E934';