Problem with product update

Question

In the query the goal is to update the unit of the product, with a subquery which retrieves the current unit from the bank and subtracts by checking by code.

UPDATE loja.produto SET UNIDADE = (SELECT (SELECT UNIDADE FROM loja.produto WHERE CODIGO = '212212121') - 1 ) WHERE CODIGO = '212212121';

I’m making the following mistake:

0 100 13:54:14 UPDATE PRODUCT SET UNIT = (SELECT (SELECT UNIT FROM store.product WHERE CODE = '212212121')-1) WHERE CODE = '212212121' Error Code: 1093. You can’t specify target table 'PRODUCT' for update in FROM clause 0.000 sec

Answer 1

I don’t know if I understand what you want but it seems to me that’s all you need:

UPDATE loja.produto SET UNIDADE = UNIDADE - 1 WHERE CODIGO = '212212121';

I put in the Github for future reference.