loki951510 Posted October 18, 2015 Share Posted October 18, 2015 (edited) i have this sql stament and i cant get it to work $sql2 = "SELECT pad_count FROM user WHERE pad_id = 1"; $sql = "UPDATE user ". "SET pad_count = ($sql2 + $pad_count) ". "WHERE pad_id = $pad_id" ; this is the error im getting from it can some one help me plz Could not update data: You can't specify target table 'user' for update in FROM clause Edited October 18, 2015 by loki951510 Quote Link to comment Share on other sites More sharing options...
benanamen Posted October 18, 2015 Share Posted October 18, 2015 UPDATE [table_name] SET [column_name] = (SELECT [column_name] FROM [table_name] WHERE [column_name] = [value]) WHERE [column_name] = [value]; Quote Link to comment Share on other sites More sharing options...
Solution Ch0cu3r Posted October 18, 2015 Solution Share Posted October 18, 2015 What are you trying to do? Increment pad_count by a certain amount? There is no need for the select query to get the current pad_count. You can reference the pad_count column in your update query and do simple arithmetic $pad_id = 1; $add_pad_count = 2; // add 2 to the current pad_count column $sql = "UPDATE user SET pad_count = (pad_count + $add_pad_count) WHERE pad_id = $pad_id"; Quote Link to comment Share on other sites More sharing options...
loki951510 Posted October 18, 2015 Author Share Posted October 18, 2015 Ch0cu3r yes that worked is their w way to echo out what it was before and after anyway like that? Quote Link to comment Share on other sites More sharing options...
Ch0cu3r Posted October 18, 2015 Share Posted October 18, 2015 Well if you are going to display the current pad_count before the update then yes you'll still need to the select query. Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted October 18, 2015 Share Posted October 18, 2015 (edited) even if you use a SELECT query to get the initial value, only use this for display purposes. any updating of the value should be done in a single query, so that you don't loose data when there are multiple concurrent instances of your code running, each trying to modify the value. or better yet, don't maintain a count/accumulator in a table column. instead, insert a separate row into a database table for each transaction that modifies a value, then sum up the values from the rows to get the current total. this will give you an audit trail so that you can detect things like double-page requests, programming errors, or if someone manages to find a security hole in your code and submits an arbitrary data value that modifies the count/accumulator by more than you want. Edited October 18, 2015 by mac_gyver Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.