UPDATE in two tables

Question

I’d like to make one update using a Inner Join.

I tried this:

UPDATE m SET m.rua = 'Rua Major Gote', b.desc_bairro = 'Centro', 
       m.id_tipo = '', m.numero = '652', m.CEP = '38700001', m.cidade = 'Patos de Minas' 
FROM tb_marker as m
INNER JOIN tb_tipo as tp 
  ON tp.id_tipo = m.id_tipo 
INNER JOIN tb_bairro as b 
  ON b.id_bairro = m.id_bairro 
WHERE m.id_marker = 1

But I got this mistake:

#1064 - You have a syntax error in your SQL next to 'FROM tb_marker as m INNER JOIN tb_type as tp ON tp.id_type = m.id_type

Is it possible to update this type? If so, how?

Answer 1

The syntax is wrong.

The order of things should be different, something like:

Update `tabela`
Inner Join `OutraTabela`
  On CondicaoJoin

Set Campo = 'Valor'
Where Condicao

Applying to your current code

UPDATE tb_marker m

INNER JOIN tb_tipo as tp 
  ON tp.id_tipo = m.id_tipo 
INNER JOIN tb_bairro as b 
  ON b.id_bairro = m.id_bairro 

SET m.rua = 'Rua Major Gote', 
    b.desc_bairro = 'Centro', 
    m.id_tipo = '', 
    m.numero = '652', 
    m.CEP = '38700001', 
    m.cidade = 'Patos de Minas' 

WHERE m.id_marker = 1