0

I have a column with sets of numbers like 21,8,0,345,... if I wanted to +1 to only one element in the set, for example element 3, would I be able to do that with a mysql query?

I know I can do this with php code (explode the string to an array then update it) but I'm afraid that with multiple updates simultaneously on the same row the values will be rewritten. First query will set 21,8,1,345 and the second will rewrite it with 21,9,0,345

Replacing the element in question might also not work because some rows have multiple elements with the same value like 2,40,40,41

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Saleh Altahini
  • 373
  • 2
  • 12
  • You should almost certainly not be storing the numbers as CSV data if you plan to do granular things like incrementing one number in the set. Instead, break out the CSV data into separate normalized rows. Then the update will be easy. – Tim Biegeleisen Nov 22 '17 at 01:21
  • the problem is that the number of elements varies from row to row. I have rows with only on value stored in this columns and others with more than 20 values. instead of creating infinite number of columns I stored them as CSV. if you have any suggestions on how to store such data it would be much appreciated. – Saleh Altahini Nov 22 '17 at 01:25
  • Create a second table and store one value per row, referencing the corresponding parent row in your original table. This is elementary relational database design. – Bill Karwin Nov 22 '17 at 02:28
  • See also my answer to https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad – Bill Karwin Nov 22 '17 at 02:29

1 Answers1

0

MySQL supports "SELECT ... FOR UPDATE" specifically for this situation to make sure the row isn't overwritten while you're processing the the row contents.

https://dev.mysql.com/doc/refman/5.7/en/innodb-locking-reads.html

The above link even gives a very similar example (except for exploding the elements, increasing the one you want, and imploding them back together).

SELECT counter_field FROM child_codes FOR UPDATE;
UPDATE child_codes SET counter_field = counter_field + 1;

The better answer, as Tim suggested, is to store this data in a separate table, especially since you have a variable number of items for each row. I don't know how you currently know that you want to update, say, the 3rd item but I'll assume that's known.

Let's say these numbers are temperature readings from various sensors at a "location" and they gradually go up and down. Your main table is "locations" with with fields:

id (int, auto-increment), location_name (varchar), ...

You're then going to create a new table called "readings" with fields:

id (int, auto-increment), location_id (int), temperature (smallint)

The "id" from the first table is going to match up to the "location_id" of many records in "readings".

When you want to add a new temperature reading to a location (I'm assuming you'll have a $location_id and $new_reading variables in PHP):

INSERT INTO readings (location_id, temperature)
VALUES ( $location_id, $new_reading )

(NOTE: You should be properly sanitizing your inputs, using PDO, or other library, but that's out of scope for this answer or I'm going to be here all night. :-) )

Let's say you want to update the 3rd reading for this location, that would mean the "offset" is 2 and you only want to update 1 record so that's what "LIMIT 2, 1" means below. (I tried and failed to find a way to do this in only 1 query; UPDATE does not seem to support offsets, at least not in my version of MySQL.)

SELECT id FROM readings WHERE location_id = 1 ORDER BY id LIMIT 2, 1;
/* Let's say you stored the above result in $reading_id */
UPDATE readings SET temperature = temperature + 1 WHERE id = $reading_id;
Colin
  • 351
  • 6
  • 16
  • This answer is very far from the requirements of the OP. – Tim Biegeleisen Nov 22 '17 at 01:40
  • Is it? He stated his reason for not wanting to use PHP was only that another session could write to the record while he was processing. (I'm happy to remove it if you really feel it's incorrect. I'm really just getting started with answering.) – Colin Nov 22 '17 at 01:41
  • The OP wants to take a CSV string `21,8,1,345` and change it to `21,9,1,345`. How does your answer help with that? – Tim Biegeleisen Nov 22 '17 at 01:49
  • My thought was that he wasn't familiar with locking reads. With this piece, he can apply the knowledge he said he had to SELECT (but now with FOR UPDATE), explode the string, increase the one value, then UPDATE the table. To me "FOR UPDATE" feels like the piece he's missing. – Colin Nov 22 '17 at 01:58
  • 1
    No, the whole idea of storing data in CSV format is wrong. – Tim Biegeleisen Nov 22 '17 at 02:02
  • Oh, I can't argue with you there; Seems like a mess. But I don't know why he's doing it, or if this field is part of a larger system that he can't refactor at this stage. I thought the main purpose of an answer should be to solve the problem the poster said they had, and the problem he seems to have is how to update one-of-many comma-delimited numbers without risking overwrite. But I totally agree that storing data this way working completely against the nature of the data. – Colin Nov 22 '17 at 02:08
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/159536/discussion-between-colin-and-tim-biegeleisen). – Colin Nov 22 '17 at 02:16