Update with Join MYSQL

Question

I have a table with customer data, with the 'broker name' and 'codbroker' fields. I need to update the codbroker from the data of my user table, where I have 'name' and 'codusuario'. So that it’s something like codcorretor=codusuario where nomecorretor=nome

I tried to do it this way but it didn’t happen:

update clientes set cli.codcorretor=us.codusuario
inner join usuario us on cli.nomecorretor=us.nome
where us.codusuario in (select codusuario from usuario);

Can someone help me ?

Thank you.

Answer 1

Us UPDATEs with JOIN, the JOINshould come before the SET. Soon, your query would look like this::

update clientes
inner join usuario us on cli.nomecorretor = us.nome
set cli.codcorretor = us.codusuario
where us.codusuario in (select codusuario from usuario);