Update by value difference per id

Question

I am trying to perform an update through this select, where I see what are different data from the pen_registration table, but when trying to perform an update, it presents errors:

[MYSQL]

UPDATE:

begin transaction
UPDATE pen_cadastro as CADAS
left OUTER JOIN novosusuarios 
ON novosusuarios.id <> CADAS.id 
set
CADAS.id = novosusuarios.id
group by novosusuarios.id

SELECT(This is working):

SELECT novosusuarios.*
FROM pen_cadastro 
left OUTER JOIN novosusuarios
ON novosusuarios.id <> pen_cadastro.id 
group by novosusuarios.id

That is, I need to do an update according to the ID difference from one table to another, if there are no newUsuarios in the pen_registration table then it will insert the values in the set fields that I point to.

Answer 1

You have to use a reference from your table when it involves more than one table , that is, use a Join.

Try it like this.

update CADAS
set CADAS.id = novosusuarios.id
FROM pen_cadastro as CADAS
left OUTER JOIN novosusuarios as novosusuarios
ON novosusuarios.id <> CADAS.id 
group by novosusuarios.id