Error involving subquery in SQL UPDATE statement

Question

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.

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

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?

Thanks.

possible duplicate of [You can't specify target table for update in FROM clause](http://stackoverflow.com/questions/4429319/you-cant-specify-target-table-for-update-in-from-clause) — sstan, Jul 11 '15 at 04:23
You are trying to update a table and you are using that table inside your subquery. You are trying to select from a table that is being updated, that's not possible. That's the error you are getting. Check how to update queries using JOIN: http://stackoverflow.com/questions/982919/sql-update-query-using-joins — Mindastic, Jul 11 '15 at 04:23
@Mindastic: I don't understand the relevance of the link to OP's problem. And it's a SQL Server post at that. Otherwise, your explanation in the comment is correct. — sstan, Jul 11 '15 at 04:28
@sstan: Because he probably could use JOINS to perform the query he wants. — Mindastic, Jul 11 '15 at 04:42

score 1 · Accepted Answer · answered Jul 11 '15 at 04:44

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

answered Jul 11 '15 at 04:44

Mindastic

4,023
3
19
20

Thanks, this works. The statement seems a little odd to me though because we're telling MySQL that we want to update a table that doesn't really exist in the database, but rather is made from other tables that do. How is SQL thinking about this? Are we allowed to make changes to any table used in the join when updating a joined table? – dsaxton Jul 11 '15 at 05:43
I am not sure about how it internally does, but i assume it maps the results to the tables and based on yout `SET`, it sets the values. Don't forget that you have to set the table that you want to update the field, for example `SET user.id`. Regarding your second question, yes, you could have updated a record from the table `ORDERS_TBL` too. Check this link (https://dev.mysql.com/doc/refman/5.0/en/update.html) and this link (http://www.mysqltutorial.org/mysql-update-join/) for more information and examples. – Mindastic Jul 11 '15 at 14:19

Error involving subquery in SQL UPDATE statement

1 Answers1