Issue in self updating sql query

Question

I am trying to update a table according to values from an inner join on the same table and another table.

Below is the query :-

update coin_txn_normalization  cn 
SET cn.is_merchant_pay_rel='H' 
where cn.coin_txn_id IN(
   SELECT ctn.coin_txn_id 
   FROM hold  h 
   INNER JOIN coin_txn_normalization ctn
   ON h.txn_id = ctn.coin_txn_id
);

I am getting the following error

SQL Error (1093): Table 'cn' is specified twice, both as a target 
for 'UPDATE' and as a separate source for data 
Affected rows: 0  Found rows: 0  Warnings: 0  Duration for 0 of 1 query: 0.000 sec.

How should I solve this error. Let Me know if anything else is needed.

See: [Why should I provide an MCVE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) — Strawberry, Jul 04 '17 at 13:22
Possible duplicate of [MySQL Error 1093 - Can't specify target table for update in FROM clause](https://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause) — Kostis, Jul 04 '17 at 13:24

score 2 · Accepted Answer · answered Jul 04 '17 at 13:22

As you have learned, MySQL doesn't all the table being updated (or deleted) to be referenced in the where or set clauses.

In your case, this is easily fixed:

update coin_txn_normalization  cn 
    set cn.is_merchant_pay_rel = 'H' 
    where cn.coin_txn_id in (select h.txn_id from hold);

This is a much simpler version anyway. If there are not multiple matches in hold, then a join is also appropriate:

update coin_txn_normalization cn join
       hold h
       on h.txn_id = cn.coin_txn_id
    set cn.is_merchant_pay_rel = 'H' ;

Issue in self updating sql query

1 Answers1