How to update the first row of a table where condition matches in every row

Question

hi i am using a update query to update a first row of a table where i am using condition to match the row with product_id. here is the query

UPDATE `product_option` SET `input_value`='$color_a',`input_price`='$color_price_a' WHERE `product_id`='$_REQUEST[pid]' and `input_type`='option' LIMIT 0,1

and this is the table

enter image description here

here the where condition matches the 7 rows with product_id=38, when i am updating it without limit so it will update the all the rows who is having the product_id=38 but i want to update only first row. how can i do this. pls answer if anybody faced this problem before.

possible duplicate of [UPDATE query is not working with LIMIT](http://stackoverflow.com/questions/10415478/update-query-is-not-working-with-limit) — ypercubeᵀᴹ, May 02 '12 at 16:17

Madara's Ghost · Answer 1 · 2012-05-02T16:13:57.197

4

Set LIMIT to 1 and sort by id Ascending. Like so:

UPDATE `product_option`
SET `input_value`='$color_a',
    `input_price`='$color_price_a'
WHERE `product_id`='$_REQUEST[pid]'
  and `input_type`='option'

ORDER BY `id` ASC

LIMIT 0,1

Though take note you should REALLY use prepared statements for this problem!

edited May 02 '12 at 16:13

answered May 02 '12 at 15:59

Madara's Ghost

172,118
50
264
308

@HarishKumar: What is failing? Do you get some sort of error in PHP? – Madara's Ghost May 02 '12 at 16:07
no not getting any error? it just ignore that and do nothing with that query when i was tested without LIMIT 1 then it updates the all that rows which is having the $_REQUEST[pid] ie 38 – Harish Kumar May 02 '12 at 16:11
no its also not worked :(, i have read some article on internet also they said that LIMIT clause is not work with UPDATE query is that so? – Harish Kumar May 02 '12 at 16:15
What article? You asked an almost identical question an hour ago and you had more than one answers with this specific information. – ypercubeᵀᴹ May 02 '12 at 16:19
And you asked another (identical) question? This is not good. You should edit your pevious question, explain ebtter what exactly is not working, what you have done and what not (as; **Have you added the `ORDER BY` suggested in the upvoted answer there?). – ypercubeᵀᴹ May 02 '12 at 16:29
And delete this question, please. – ypercubeᵀᴹ May 02 '12 at 16:30

score 0 · Answer 2 · answered May 02 '12 at 16:01

0

you should define what FIRST means. then you can order by that and LIMIT 1 or explicitly match to the MIN in your where clause.

answered May 02 '12 at 16:01

Randy

16,480
1
37
55

how can i define the first row? – Harish Kumar May 02 '12 at 16:07
1

what makes it the first row? SQL is set based. – Randy May 02 '12 at 16:14

score 0 · Answer 3 · answered May 03 '12 at 05:15

Ok so i have finally solve this issue. The Update query does not work with the LIMIT OFFSETS like if you will put LIMIT 0,1 it will not work. Either it will work without LIMIT or if you will put the LIMIT so you can use only if you have to change the multiple rows with LIMIT 2 or more....

In my case i want to update only a perticular row for that i have used the

<input type="hidden">

inside this hidden type i called the value of the row and the row which i want to update i have called the value from this hidden input like this

"UPDATE `product_option` SET `input_value`='$weight_d',`input_price`='$weight_price_d' WHERE `product_id`='$_REQUEST[pid]' and `input_type`='checkbox' and `input_value`='$_POST[hide_weight_d]' and `input_price`='$_POST[hide_weight_price_d]'"

here in this query $_POST[hide_weight_d] is the same hidden value of $weight_d so it matches the value on table and update it.

How to update the first row of a table where condition matches in every row

3 Answers3