mysql - Error involving subquery in SQL UPDATE statement -

i'm trying update value of field specific row in mysql i'm getting error don't understand.

here have 2 tables customer_tbl , orders_tbl, , want update customer_tbl cust_name customer made order ord_num equal 23e934 'davids market'.

here 2 tables:

mysql> describe customer_tbl; +--------------+-------------+------+-----+---------+-------+ | field        | type        | null | key | default | | +--------------+-------------+------+-----+---------+-------+ | 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 | | +----------+-------------+------+-----+---------+-------+ | 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 code gives error:

mysql> update customer_tbl      -> set cust_name = 'davids market'      -> cust_id = (select c.cust_id      -> customer_tbl c,      -> orders_tbl o      -> c.cust_id = o.cust_id      -> , o.ord_num = '23e934'); error 1093 (hy000): can't specify target table 'customer_tbl' update in clause 

what's issue referring customer_tbl in subquery, , how around this?

thanks.

you like:

update customer_tbl inner join orders_tbl  on customer_tbl.cust_id = orders_tbl.cust_id  set customer_tbl.cust_name = 'davids market'  orders_tbl.ord_num = '23e934';