MySQL Forums
Forum List  »  General

Re: Query Error
Posted by: Peter Brawley
Date: September 01, 2016 10:56AM

Yelling doesn't help.

To preserve formatting in a posted query, surround it with BBCode [ code ] and [ /code ] tags (without the spaces). Look up BBCode for more information.

Formatting your query for readability ...

UPDATE table1.trafico 
SET table1.trafico.RDocumentos=
 (SELECT RD.Folio 
  FROM table2.vt_rdocumentos     RD 
  JOIN table2.vt_rdocumentosdeta RDD ON RD.ID=RDD.RDocumentos 
  JOIN table2.vt_pedimentos      P   ON RDD.PedimentoID=P.ID 
  JOIN table2.vt_clientes        C   ON C.ID=P.Cliente 
  LEFT JOIN table1.trafico       T   ON T.RDocumentos=RD.Folio 
  WHERE RD.Placas='M3' 
    AND C.Numero='GM064' 
    AND RD.FechaEmision=CURRENT_DATE 
    AND NOT EXISTS (
      SELECT table1.trafico.RDocumentos 
      FROM table1.trafico 
      WHERE table1.trafico.RDocumentos=RD.Folio
    ) 
  GROUP BY RD.Folio
 ), 
 table1.trafico.PapelesDespacho=1 , 
 table1.trafico.HorapapelDesp=CONCAT(CURRENT_DATE,' ',CURRENT_TIME) 
WHERE table1.trafico.ID='76' 
  AND DATE(table1.trafico.HorafacturaRec)=CURRENT_DATE 
  AND table1.trafico.PapelesDespacho=0 
  AND table1.trafico.Despachado=0 
  AND table1.trafico.Rojo=0 
  AND table1.trafico.Rojo2=0 
  AND table1.trafico.Libre=0 ;

The error message means exactly what it says: you can't specify a target table for update in a FROM clause. As the MySQL documentation page on Update says.

The simplest solution to your problem may be to turn that update statement into a SELECT, and when you're sure its results are correct, save the result to a temp table, then update from that temp table.

Options: ReplyQuote


Subject
Written By
Posted
August 31, 2016 06:13PM
Re: Query Error
September 01, 2016 10:56AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.