SQL Query SET Statement issue with nested Statements in PHP

####The Details
I have a table in a database. In the table, there are the following columns:

  • id

  • name

  • score

####What I am trying to do
To 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);
");

####Expected Result
The “name” and “score” of row with “id” 1 is changed to “$name” and “$score” respectively

####Actual Result
The “name” and “score” were not changed

####additional information 1
To 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 2
To 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 3
Out 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.

####Summary
It 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.

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.