You can't specify target table 'prodt' for update in FROM clause

Question

Its PHP MYSQL :

I have a table prodt , in which i first INSERT a value and with its LAST INSERT ID i do update for MAX + 1 as below , BUT I AM GETTING ERROR

   $a = db_insert_id();

$sqllast = "UPDATE prodt SET  
            pdname= ((SELECT pdname FROM ( SELECT MAX( pdname ) AS pdname
            FROM prodt
            WHERE oid = ".db_escape($oid)."  ) AS pdname ) + 1  ),
            pcyn =  ".db_escape(0)."
            WHERE id = ".db_escape($a)." 
            AND oid= ".db_escape($oid)." ";

            $resultsqllast = db_query($sqllast);

            if((!$resultsqllast) || (db_mysql_affected_rows($db) <= 0)) {
               throw new Exception('Wrong SQL UPDATE' . $sqllast . ' Error: '.db_error_msg($db) . db_error_no()); 
            }

NOTE : pdname is not string ...its number only eg: 1234 , hence 1234+1 will output me 1235

After research i tried below :

$sqllast = "UPDATE prodt SET  
            pdname= ((SELECT pdname FROM ( SELECT MAX( pdname ) AS pdname
            FROM ( SELECT * FROM prodt
            WHERE oid = ".db_escape($oid)." )AS pdname ) AS pdname ) + 1  ),
            pcyn =  ".db_escape(0)."
            WHERE id = ".db_escape($a)." 
            AND oid= ".db_escape($oid)." ";

           $resultsqllast = db_query($sqllast);

            if((!$resultsqllast) || (db_mysql_affected_rows($db) <= 0)) {
               throw new Exception('Wrong SQL UPDATE' . $sqllast . ' Error: '.db_error_msg($db) . db_error_no()); 
            }

But still its not working...getting still same message ...please helpme

Note : Above first query was working fine in my WAMP2.5 version , today only i update to WAMP3.0 and start getting error for same ....

Thanks

few examples here: http://stackoverflow.com/questions/2334712/update-from-select-using-sql-server — xQbert, Jun 06 '16 at 12:12
What is the error your 2nd query is giving you? Is it the same error? — Keeleon, Jun 06 '16 at 12:15
same what is above..... SORRY it give me every " Every derived table must have its own alias" hence i did that but again it gave me same error... please see update question — user3209031, Jun 06 '16 at 12:17
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) — Jocelyn, Jun 06 '16 at 12:50
Hey @jocelyn , dont you see i have treid that way .... i have tried above link ...but still didnt got answer hence posted another one .... — user3209031, Jun 06 '16 at 12:56

score 0 · Answer 1 · answered Jun 06 '16 at 12:56

As the first error did tell, you cannot use a sub-query to select from the same table as you want to update. So you will have to split this into two queries or see if you can handle it when you insert data. In your 2nd example you have used the alias pdname on 2 subqueries, that is why you get that each derived table must have its own alias.

I am not sure if I understand your table correct is pdname a string or number? If is is a string and you are adding the integer 1 to it the result might not be what you expected if it is an integer i suggest renaming in. Also what is oid? If it is an id that only return your new row you might be able to change your query to something like:

UPDATE prod SET pdate = pdate+1 WHERE id = <rest of your query>

Sorry is not string ...its number only eg: 1234 , hence 1234+1 will output me 1235 — user3209031, Jun 06 '16 at 12:57
Are you the updating the same row as you insert, or can it be any other row? If it is the same row you can use the query I suggested, or auto increment. If it is a different row you have to split your query into two queries, one to find the row to update and another to update it. — rypskar, Jun 07 '16 at 06:28

You can't specify target table 'prodt' for update in FROM clause

1 Answers1