Update with JOIN in MySQL

1

I'm making an update to table A which will receive values from Table B

as a.codigo = B.folha When running the routine below, it is slow and nothing happens for a long time. There are 500,000 lines. It's normal or I have to wait or the script is wrong ... I do not know what to do in the routine in fact I have to bring information from B to A as per the condition.

UPDATE escrituras t1 INNER JOIN 
       imovel t2 ON t2.LIVRO = t1.CODIGO
   SET  
         t1.TipoImovelDescr = t2.DESCRICAO;
            a.RUA            =  b.RUA;
            a.BAIRRO      =  b.BAIRRO,
            a.MUNICIPIO = b.CIDADE,
            a.UF               = b.ESTADO
    
asked by anonymous 02.02.2015 / 06:02

1 answer

2

The query has some syntax errors such as the alias a and b a semicolon remaining on the first line after the set, your query should look like this.

UPDATE escrituras t1 INNER JOIN 
       imovel t2 ON t2.LIVRO = t1.CODIGO
   SET  
      t1.TipoImovelDescr = t2.DESCRICAO,
      t1.RUA = t2.RUA,
      t1.BAIRRO =  t2.BAIRRO,
      t1.MUNICIPIO = t2.CIDADE,
      t1.UF = t2.ESTADO

Maybe the problem is the lack of a WHERE some banks cancel these update / delete statements as this can cause a lot of damage, so imagine upgrading the descriptions / categories of all products to sal/condimentos . In mysql you can enable this by adding this line before your main query:

SET SQL_SAFE_UPDATES=0;
    
02.02.2015 / 11:58