Jump to content
Sign in to follow this  
shashib

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

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

Share this post


Link to post
Share on other sites

Can you write a stored procedure for this? You will have a lot more abilities available in a stored procedure.

Share this post


Link to post
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 .

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
Sign in to follow this  

×
×
  • Create New...