Updating a mySQL row containing IDs

Question

I have a mySQL table that have a column read_by which has the rows:

-17-6
-11-8-6-62
-6
-6-22-45
-16-77
-31-3-6-24

These are IDs of users, Every ID starts with a dash -

Now I have changed the user 6 to 136

How to update every row and just change 6 to 136 keeping in mind that there may be IDs like -16, -62, -167... and the ID 6 is in different positions.

Here are the WHERE conditions:

WHERE read_by = "-6"
WHERE read_by LIKE "%-6"
WHERE read_by LIKE "-6-%"
WHERE read_by LIKE "%-6-%"



UPDATE messages SET [...] WHERE ...

How to change the exact 6 and keep the rest as is?

Thanks.

Never, ever store data as dash separated items. (Or comma separated etc.) It will only cause you lots of trouble. — jarlh, Aug 27 '19 at 13:24
At least don't store lists of items if you actually need to use SQL to query or update individual items in the list. It might be okay to store a list if you always just store and fetch the whole list, not individual items in the list. — Bill Karwin, Aug 27 '19 at 14:34

score 3 · Accepted Answer · answered Aug 27 '19 at 13:27

You need to append a dash to the column in order to filter and the remove it after the update:

update messages 
set read_by = trim(trailing '-' from replace(concat(read_by, '-'), '-6-', '-136-'))
where concat(read_by, '-') like '%-6-%'

See the demo.
Results:

> | read_by      |
> | :----------- |
> | -17-136      |
> | -11-8-136-62 |
> | -136         |
> | -136-22-45   |
> | -16-77       |
> | -31-3-136-24 |

score 2 · Answer 2 · answered Aug 27 '19 at 13:24

If you need to change all "6"s to "136", you can do:

update t
    set read_by = trim(both '-' from replace(concat('-', read_by, '-'), '-6-', '-136-'))
    where concat('-', read_by, '-') like '%-6-%';

Then, you should think about fixing your id. Use an auto-incremented number. Information such as "6" should be stored in a column somewhere.

Updating a mySQL row containing IDs

2 Answers2