koobas.hobune.stream
SQL Query SET Statement issue with nested Statements in PHP
I have a table in a database. In the table, there are the following columns:
id
name
score
...
What I am trying to doTo try out nested query statements in parentheses
My Statement $sth = $dbh->query
("
UPDATE scores
SET name='$name', score='$score'
WHERE id = (SELECT id FROM scores ORDER BY id ASC LIMIT 1);
");
The "name" and "score" of row with "id" 1 is changed to "$name" and "$score" respectively
Actual ResultThe "name" and "score" were not changed
additional information 1To make sure that the statement within the parentheses is working fine, I applied it to an isolated SELECT statement:
$echo = $dbh->query
("
SELECT *
FROM scores
WHERE id = (SELECT id FROM scores ORDER BY id ASC LIMIT 1);
");
The statement above worked exactly as expected... that is... to SELECT the whole row with the id 1.
additional information 2To make sure that the statement outside of the parentheses is working fine, I simplified the WHERE condition:
$sth = $dbh->query
("
UPDATE scores
SET name='$name', score='$score'
WHERE id =1;
");
The statement worked exactly as expected... that is... to change the name and score of row with id 1.
additional information 3Out of desperation, I also tried to split the PDO::query class into PDO::prepare and PDO::execute classes.
$sth = $dbh->prepare
("
UPDATE scores
SET name = '$name', score='$score'
WHERE id = (SELECT id FROM scores ORDER BY id ASC LIMIT 1);
");
and
$sth->execute($_GET);
And as expected, it did not work.
SummaryIt seemed like the (SELECT id FROM scores ORDER BY id ASC LIMIT 1) nested statement does not work in a statement with UPDATE SET only.
I have no idea what went wrong. Please provide advice. If there is any information required, do let me know.
I'm genuinely curious: How did you get to post this question here? :D Are you running a PHP script on the server side of an online unity game or something?
You haven't mentioned which database provider you are using (MySQL etc) but in the past when I have attempted this sort of query I receive a syntax error telling me that you cannot update a table that is being queried at the same time. So in this case because you are performing a select on scores you wouldn't be able to update it.
This may not be the case here depending on the provider and version etc.
In MySQL a workaround for this would be hiding the subselect in another subselect.
UPDATE scores
SET name='$name', score='$score'
WHERE id = (SELECT id FROM (SELECT id FROM scores ORDER BY id ASC LIMIT 1) as temp1);
This question is actually off-topic. Nothing about that question is about Unity.
Anyways, MySQL can not update a table that you currently read from. See this SO question. If you run that query in an actual SQL client you should actually get that error.
The easiest fix is to execute two seperate queries, one after the other, So first do your "id" select, store it in a variable and then do your update query.
