MySql Update a column based on table join - Error 1093

Question

UPDATE AMAZON 
SET AMARBELEG =  (
                   Select Rechnungen.Rechnungsnummer 
                   from Rechnungen,Amazon 
                   where Rechnungen.Belegtext = Amazon.orderid
                 );

Hello i am trying to prevent the

error code 1093.

I have tried several workarounds, but seem not to quite understand the idea of the tmptable. Could anybody explain with my Tables, how i can prevent the error?

Possible duplicate of [MySQL Error 1093 - Can't specify target table for update in FROM clause](http://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause) — jpw, Jul 13 '16 at 13:34

score 0 · Answer 1 · answered Jul 13 '16 at 13:54

0

The correct syntax is:

UPDATE AMAZON A
JOIN Rechnungen R ON R.Belegtext = A.orderid
SET A.AMARBELEG = R.Rechnungsnummer;

answered Jul 13 '16 at 13:54

jpw

44,361
6
66
86

Thanks that worked like a treat, could not see the tree within the forest anymore. – Locust Jul 13 '16 at 14:18
@Locust Since the solution in this answer is the same as in the question I flagged as duplicate consider accepting the duplicate. – jpw Jul 13 '16 at 14:22

score 0 · Answer 2 · answered Jul 13 '16 at 14:00

0

The correct syntax is:

UPDATE AMAZON 
   SET AMARBELEG = (SELECT Rechnungen.Rechnungsnummer 
                      FROM Rechnungen 
                     WHERE Rechnungen.Belegtext = Amazon.orderid);

answered Jul 13 '16 at 14:00

Prasad Dhananjaya

141
4

score 0 · Answer 3 · answered Feb 06 '17 at 19:41

Try this to prevent mysql 1093 error.

UPDATE AMAZON 
SET AMARBELEG =  ( SELECT r2.rechnr FROM 
                              ( Select Rechnungen.Rechnungsnummer 
                                   from Rechnungen,Amazon 
                                 where Rechnungen.Belegtext = Amazon.orderid
                               ) r2
                 );

MySql Update a column based on table join - Error 1093

3 Answers3