Is it possible in MySQL to update only the nth place?
My table has data like this
products_parent_id | products_quantity
2 5
2 7
2 9
2 4
My SQL statement looks like this (for now):
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, let's say only the 3rd occurrence of product_parent_id (in this case 3rd from the top 9)
To select the third occurrence I used this at the end of my LIMIT ($ grade, 1) statements
Is there a way to UPDATE using MySQL, but only in case of 3rd / 4th / etc. Will it be reliable?
thank
source to share
Short answer: No
Long answer:
Sorting. Because the order returned by the rows is not defined by SQL.
Technically, two different database queries could return rows in a different order. So even if you update the third line to be the third will be implementation dependent.
The only way to mitigate this is to apply a specific line order. (To order)
I don't think this is part of the language specification, but most SQL implementations allow you to get a specific string from a query. I'm not sure what MYSQL specific details are, but a quick google got this page:
source to share
Your question does not explain the reasons why you are doing this update, but it reminds me when I was creating sample records in a DB table and wanted to move a few of them to Category A or Category B.
There is no ORDER BY specified in your SQL query, so Martin's answer is correct, saying that you cannot get the same resulting order every time. However, if your ORDER BY was based on a certain ID and that ID was sequential, you could use modulo to access every third record.
For example:
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 this method works for you, you can do your update to hit every third entry using
UPDATE [ ... ] WHERE id % 3 = 0 ;
(Update where id / 3 has remainder 0).
source to share
You can also use the LIMIT keyword when using UPDATE with a single table reference, however I have not tested this myself.
Source: MySQL Update Syntax
source to share