SQL: replace with few records (mysql)

Question

There's an index table. (Links tags with ids).

REPLACE INTO com_index (word, ids) VALUES ('word1', concat(ids, ' 2')), ('word2', concat(ids, ' 2'))

word is a primary key.

I try to look through rows, add ' 2' to ids in those of them, which exist; and create a new row with ' 2' if it doesn't.

So, I need to look if there's any row with such word column (word1 or word2 ...) and concat ' 2' to thier ids. And if there is no such rows (with such word column) - create it with ids = ' 2'.

And it's MySQL! (there's no sub_selects)

Answer 1

replace into inserts or updates entire rows. I don't think you can use it to do an update that depends on the current value of a column.

You could do it in two statements, like:

UPDATE com_index SET ids = concat(ids,' 2')  
    WHERE word = 'word1'
    AND ids <> '2'           -- Only 2
    AND ids not like '2 %'   -- Starts with 2
    AND ids not like '% 2 %' -- 2 in the middle
    AND ids not like '% 2';  -- 2 at the end
INSERT INTO com_index (word, ids) 
    SELECT 'word1', '2'
    FROM dual
    WHERE NOT EXISTS (select * from com_index where word = 'word1');

As you can see, storing a list in a column gets tricky fast. Consider splitting the com_index table into two tables with a foreign key relation.

Answer 2

A very simple solution that is possible because word is the primary key*:

INSERT INTO `com_index` (word, ids)
VALUES ('word1', '2'), ('word2', '2')
ON DUPLICATE KEY UPDATE ids=concat(ids, ' 2')

Make sure to check the MySQL Manual for the INSERT ... ON syntax

---

*actually, it is also possible with other kinds of indexes, as long as they're unique (which is given with a primary key