Mysql SELECT and UPDATE in same command

[loki951510]

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

[benanamen]

UPDATE [table_name] SET [column_name] = (SELECT [column_name] FROM [table_name] WHERE [column_name] = [value]) WHERE [column_name] = [value];

[Ch0cu3r]

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";

[loki951510]

 

Ch0cu3r yes that worked is their w way to echo out what it was before and after anyway like that?

 

[Ch0cu3r]

Well if you are going to display the current pad_count before the update then yes you'll still need to the select query.

[mac_gyver]

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