UPDATE in two tables at the same time

Asked

Viewed 26 times

-2

I need to perform a Update in two tables at the same time, each of which contains two fields that will be changed. However, it is not working!

Returns error in INNER

Look how I thought:

ID_ESTOQUE = PK;
ID_IDENTIFICADOR = FK

UPDATE tb_estoque estoque 
INNER JOIN tb_est_produto produto ON estoque.id_estoque = produto.id_identificador 
SET estoque.id_cti = '004', estoque.cfop = '5102', produto.cod_ncm = '11111111', produto.cst = '020' 
WHERE id_estoque = 1;
mysql sql sql-update
  • 1

    and what error? can give error if there is the column "id_stock" in the two tables, maybe WHERE estoque.id_estoque = 1;?

1 answer

0

You need to separate the changes from each table:

UPDATE tb_estoque
SET id_cti = '004', cfop = '5102'
WHERE id_estoque = 1;

UPDATE tb_est_produto
SET cod_ncm = '11111111', cst = '020' 
WHERE id_identificador = 1;

NOTE: for security you can use a TRANSACTION, preventing one table to update and another not.

START TRANSACTION;

--updates

COMMIT;

Edit

I had said that it is not correct/possible to update two tables at the same time, but as well as comment below @Ricardo Pontual, "you can add more than one table in update and update them in a single command"

  • 1

    "You cannot update two tables in a single UPDATE; conceptually it is wrong" sorry but this is not correct, you can add more than one table in the update and update them in a single command

Browser other questions tagged mysql sql sql-update

You are not signed in. Login or sign up in order to post.