Jump to content

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


shashib
 Share

Recommended Posts

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

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

       $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());
                }


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 same error message ...

Thanks

Link to comment
Share on other sites

i think this is what you are trying to do :

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

hope this helps .

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...