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