1

hi i am using an UPDATE query with limit 0,1 where i have to update the first row which matches the condition which i m giving in the query

mysql_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");

in my sql table the $_request[pid] is repeating many times so on that rows in which $_request[pid] is matching, i want to update the first row on it but this query is not doing any thing the data is still same which i have added.

any suggestion will be appreciated

here is the image of the table

enter image description here

Harish Kumar
  • 927
  • 3
  • 20
  • 46
  • What do you mean with *"**first** row that matches ..."*? There is no order in a relational table. If uoi want to use `LIMIT`, you should provide an `ORDER BY` as well. Otherwise, a random row (that matches the criteria) will be updated. – ypercubeᵀᴹ May 02 '12 at 14:14
  • 5
    Please bear in mind that updating with Limit is not replication safe. In other words, in a replicated mysql environment there is the potential for the command to update a different row on the master than it did on the slave http://dev.mysql.com/doc/refman/5.1/en/replication-features-limit.html – Anigel May 02 '12 at 14:14
  • this might help http://stackoverflow.com/questions/6289729/mysql-update-query-with-limit – sree May 02 '12 at 14:16
  • Take a look at this thread: http://stackoverflow.com/questions/1513206/update-multiple-rows-using-limit-in-mysql LIMIT is not supported with UPDATE alone. –  May 02 '12 at 14:18
  • `LIMIT` is supported. `LIMIT` with `OFFSET` is not. – ypercubeᵀᴹ May 02 '12 at 14:29
  • Why would you need to do this? There are antoincrement and unique column types. Use that to update only one row. – machineaddict May 30 '13 at 08:27

1 Answers1

5

It should be LIMIT 1

LIMIT 0,1 means OFFSET 0 LIMIT 1, but UPDATE does not support offsets.

Even if it would work (which it doesn't) you still always have to supply an ORDER clause or it will be a random row.

PeeHaa
  • 71,436
  • 58
  • 190
  • 262
Maxim Krizhanovsky
  • 26,265
  • 5
  • 59
  • 89
  • no actually limit 0,1 is working when i am calling the data from table it displays the first from the multiple rows which matches the conditions. i dont know on updating its working or not – Harish Kumar May 02 '12 at 14:14
  • @HarishKumar see my edited answer; is's valid syntax for selects but not for updates – Maxim Krizhanovsky May 02 '12 at 14:15
  • @Harish: `LIMIT 0,1` works with `SELECT` but not with `UPDATE`. – ypercubeᵀᴹ May 02 '12 at 14:16
  • i have upload the table image also this will help i think for understanding what i need to do. here i want to update the first row so can it be possible with update query – Harish Kumar May 02 '12 at 14:59