Is it possible in MySQL to update only the nth occurence?

Question

My table has data like so

  products_parent_id | products_quantity
        2               5
        2               7
        2               9
        2               4

My SQL statement looks like so (so far):

UPDATE ' . TABLE_PRODUCTS . ' SET products_quantity = products_quantity +' . $order['products_quantity'] . ', products_ordered = products_ordered - ' . $order['products_quantity'] . ' WHERE products_parent_id = ' . (int)$order['products_id']

Now what I want to do, is update only say the 3rd occurrence of products_parent_id (in this case, 3rd from the top is 9)

To SELECT the 3rd occurrence, I used this at the end of my statements LIMIT($grade, 1)

Is there a way to UPDATE using MySQL but only the 3rd/4th/etc occurrence? Will it be reliable?

Thank you

Answer 1

You can also use the LIMIT keyword when using UPDATE with the single table reference, however I have not tested this myself.

Source: MySQL Update Syntax

Answer 2

You can also use record counting subquery, as in

   Update Table T Set
     Col = value 
   Where (Select Count(*) From Table 
          Where products_quantity <= T.products_quantity)
          = 3

Answer 3

Short Answer: No

Long Answer:

Sort Of. Because the order the rows are returned is not defined by SQL.
Technically two different requests to the DB could return the rows in a different order. So even if you update the third row, which is the third will depend on the implementation.

The only way to mitigate this is to apply a specific ordering to the rows. (Order by)

I don't think it is part of the language specification but most SQL implementations allow you to get a particular row from a query. I am not sure what the MYSQL specific details are but a quick google got this page:

http://forums.mysql.com/read.php?10,36490,36511

Answer 4

Your question doesn't make your reasons for doing this update entirely clear, but it does remind me of when I've been generating sample entries in a DB table and wanted to move a handful of them into category A or category B.

Your SQL query doesn't specify an ORDER BY, so Martin's answer is correct in saying that you may not get the same result order each time. However, if your ORDER BY was based on a particular ID, and that ID was sequential, you would be able to use modulo to access each third record.

Eg:

mysql> create table foo ( id int ) ;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into foo values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (15) ;
Query OK, 12 rows affected (0.00 sec)
Records: 12  Duplicates: 0  Warnings: 0

mysql> select * from foo where id%3=0;
+------+
| id   |
+------+
|    3 | 
|    6 | 
|    9 | 
|   15 | 
+------+
4 rows in set (0.00 sec)

If that method works for you, you could do your update to hit every third entry by using

UPDATE [ ... ] WHERE id % 3 = 0 ;

(Update, where id / 3 has a remainder of 0).