CODEWITHSUNDEEP
phpmysqlsql
dshukertjr
dshukertjr

Reputation: 18740

sql updating only one row

I am trying to update only one row using sql, but I am having troubles with it.

I am trying to do something like this:

$sql="UPDATE table SET age='$age' WHERE id=(SELECT id FROM another_table WHERE somecondition ORDER BY id LIMIT 1)";

but this is not updating anything. I feel like there is some error with where the parenthesis are, but I am not sure what exactly is wrong with it. Does anybody have any idea? or have other suggestions on how to update only one row that satisfies the given conditions?

Edited Notes:

Okay, I may have made my question too complicated. Let me rephrase my question; What is the generic way of updating only 1 row that meets certain conditions. It can be any row if the row meets the conditions.

Upvotes: 1

Views: 8610

Answers (3)

Brian Payne
Brian Payne

Reputation: 424

I have found that updating based on a condition in a sub-query, as in your example, sometimes has problems that seem due to the database trying to figure out the best execution path. I have found it better to do something like the following, noting that my code is in T-SQL and may need a smidgen of tweaking to work in MySQL.

UPDATE T1 SET age=@Age
FROM table as T1 INNER JOIN
     another_table as T2 ON T1.id = T2.id
WHERE [use appropriate conditions here]

Upvotes: 1

Gouda Elalfy
Gouda Elalfy

Reputation: 7023

you should run this query firstly:

SELECT id FROM another_table WHERE somecondition ORDER BY id LIMIT 1

and see the result, if you get specific value, say for example 1 , update your code to be

$sql="UPDATE table SET age='$age' WHERE id=(1)";

and you can see the results. if the query doesn't produce errors so your condition doesn't consider and there is no 1 id in your table table.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1271003

Try running this query:

UPDATE table t
    SET t.age='$age'
    WHERE t.id = (SELECT a.id
                  FROM another_table a
                  WHERE somecondition
                  ORDER BY a.id
                  LIMIT 1
                 );

One not-uncommon cause of this error is when the id column has different names. You should get in the habit of qualifying column names. You should also verify that the ids in the two tables are intended to match.

Another cause would simply be that the matched conditions return no row or ids that are not in the table. That is a bit harder to fix, which better understanding the data and data structure.

Upvotes: 0

Related Questions