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

I'm trying to update the value of a field for a specific row in MySQL but I'm getting an error that I don't really understand.

我正在尝试更新MySQL中特定行的字段值,但是我收到的错误我并不理解。

Here we have two tables CUSTOMER_TBL and ORDERS_TBL, and I want to update CUSTOMER_TBL so that the CUST_NAME for the customer who made the order with ORD_NUM equal to 23E934 is 'DAVIDS MARKET'.

这里我们有两个表CUSTOMER_TBL和ORDERS_TBL,我想更新CUSTOMER_TBL,以便订购ORD_NUM等于23E934的客户的CUST_NAME是'DAVIDS MARKET'。

Here are the two tables:

以下是两个表格:

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)

And the code that gives the error:

以及给出错误的代码:

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

What's the issue with referring to CUSTOMER_TBL in the subquery, and how would I get around this?

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

Thanks.

1 个解决方案

#1

You could probably do something like:

你可以做一些像:

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';