shashib Posted June 6, 2016 Share Posted June 6, 2016 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 ERRORYou 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 More sharing options...
davej Posted June 6, 2016 Share Posted June 6, 2016 Can you write a stored procedure for this? You will have a lot more abilities available in a stored procedure. Link to comment Share on other sites More sharing options...
john_jack Posted June 7, 2016 Share Posted June 7, 2016 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 More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now