PHP/MYSQL:Carry out UPDATE within SELECT query

Question

There are many questions on SO about this but I cannot find one that quite meets my situation.

I want to use the values in some fields/columns of a table to set the value of a third field/column

In other words something like:

table races
athleteid|difficulty|score|adjustedscore

$sqlSelect = "SELECT athleteid,difficulty,score FROM races";
$res = mysql_query($sqlSelect) or die(mysql_error());
while ($row = mysql_fetch_array($res)){

$adjustedscore=difficulty*score;

$sqlupdate = "UPDATE race, set adjustedscore = '$adjustedscore' WHERE athletes = 'athletes'";
$resupdate = mysql_query($sqlupdate);

}

My understanding, however, is that MYSQL does not support update queries nested in select ones.

Note, I have simplified this slightly. I am actually calculating the score based on a lot of other variables as well--and may join some tables to get other inputs--but this is the basic principal.

Thanks for any suggestions

instead of update nested in select would you not have a select nested inside of the update query? — camdixon, Feb 13 '17 at 21:33
that might be the way to do it. But it would have to be in a loop through ids as I need to do this for all of the records. — zztop, Feb 13 '17 at 21:34
***Please [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php).*** [These extensions](http://php.net/manual/en/migration70.removed-exts-sapis.php) have been removed in PHP 7. Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really pretty easy](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Feb 13 '17 at 21:36
[Little Bobby](http://bobby-tables.com/) says ***[your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)***. Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! — Jay Blanchard, Feb 13 '17 at 21:37
Change this:`set adjustedscore = '$adjustedscore WHERE`... for this `set adjustedscore = '$adjustedscore' WHERE`... — Danilo Bustos, Feb 13 '17 at 21:40

score 2 · Answer 1 · answered Feb 13 '17 at 21:53

2

You can run:

UPDATE `races`
SET `adjustedscore` = `difficulty` * `score`
WHERE `athleteid` IN (1, 2, 3, ...)

answered Feb 13 '17 at 21:53

Jirka Hrazdil

3,983
1
14
17

score 0 · Accepted Answer · answered Feb 13 '17 at 22:04

First of all, as previous commentators said, you should use PDO instead of mysql_* queries. Read about PDO here.
When you'll get data from DB with your SELECT query, you'll get array. I recommend you to use fetchAll() from PDO documentation.
So, your goal is to save this data in some variable. Like you did with $row.
After that you'll need to loop over each array and get your data:
foreach($row as $r) { //We do this to access each of ours athlete data $adjustedscore= $row[$r]["difficulty"]* $row[$r]["score"]; //Next row is not clear for me... $query = "UPDATE race SET adjustedscore = '$adjustedscore' WHERE athletes = 'athletes'";
And to update we use PDO update prepared statement
$stmt = $dbh->prepare($query); $stmt->execute(); }

Selecting this answer over Jiri's which also worked as it will be easier to do calculations in php (as I add more logic in calculating adjusted score) than in MYSQL. But both approaches seem valid. — zztop, Feb 13 '17 at 22:24

PHP/MYSQL:Carry out UPDATE within SELECT query

2 Answers2