Update text column based on content with a single lookup in SQLite

Question

In my iOS and Android projects, I have a SQLite table that looks something like this:

id    words
-----------
1     apple, banana, orange, peach, strawberry
2     car, plane, orange
3     sheep, car, plane, horse, cow
.     ...
.
.

The words column is a TEXT column that holds a comma deliminated list of words.

I want to update the word list of a particular row by adding a word to the front of the list. The list should have no more than 5 items so I also would delete the last word if necessary.

For example, if I were updating row id 1 with cherry then I would get

cherry, apple, banana, orange, peach

Or if I were doing the same update on row id 2 then I would get

cherry, car, plane, orange

My Question

I know that I could do a query to get the row, process the text, and then update the row. However, this would require two table lookups, one for the query and one for the update. Is this possible to do with a single update lookup?

I know there is the replace() function but I am not replacing anything here. I'm also not simply incrementing an integer. I didn't see anything obvious in the SQLite core functions.

Answer 1

Correct solution

The words column is a TEXT column that holds a comma deliminated list of words.

1 NF. Column contains atomic data. Normalize schema to get clean code and better performance.

Workaround solution:

SQLite does not have built-in reverse function, that is why it is a bit ugly:

CREATE TABLE mytable(id    INTEGER  NOT NULL, words TEXT );
  
INSERT INTO mytable(id,words) VALUES (1,'apple, banana, orange, peach, strawberry');
INSERT INTO mytable(id,words) VALUES (2,'car, plane, orange');
INSERT INTO mytable(id,words) VALUES (3,'sheep, car, plane, horse, cow');
INSERT INTO mytable(id,words) VALUES (4,'sheep, cherry, plane, horse, cow');

UPDATE mytable
SET words = CASE 
            WHEN (LENGTH(words) - LENGTH(REPLACE(words, ',', ''))) < 4 
            THEN 'cherry, ' || words
            ELSE  SUBSTR('cherry, ' || words, 1,
                        LENGTH(words) + LENGTH('cherry, ') -
                        LENGTH(SUBSTR(SUBSTR(
                      SUBSTR( SUBSTR(words, INSTR(words,',')+1), INSTR(SUBSTR(words, INSTR(words,',')+1), ',')+1),
                      INSTR(SUBSTR( SUBSTR(words, INSTR(words,',')+1), INSTR(SUBSTR(words, INSTR(words,',')+1), ',')+1), ',') + 1),
                      INSTR(SUBSTR(
                      SUBSTR( SUBSTR(words, INSTR(words,',')+1), INSTR(SUBSTR(words, INSTR(words,',')+1), ',')+1),
                      INSTR(SUBSTR( SUBSTR(words, INSTR(words,',')+1), INSTR(SUBSTR(words, INSTR(words,',')+1), ',')+1), ',') + 1),',')+1)) -1
                      )
            END
WHERE words NOT LIKE '%cherry%';

SELECT * FROM mytable;

SqlFiddleDemo

To make it more general you need to change cherry with your value.

Output:

╔════╦══════════════════════════════════════╗
║ id ║                words                 ║
╠════╬══════════════════════════════════════╣
║  1 ║ cherry, apple, banana, orange, peach ║
║  2 ║ cherry, car, plane, orange           ║
║  3 ║ cherry, sheep, car, plane, horse     ║
║  4 ║ sheep, cherry, plane, horse, cow     ║
╚════╩══════════════════════════════════════╝

How it works:

• UPDATE ... WHERE words NOT LIKE '%cherry%'; do not update rows that have cherry already
• SET words = CASE WHEN (LENGTH(words) - LENGTH(REPLACE(words, ',', ''))) < 4 if number of delimeters (commas) is lower than 4 just concatenate value to string
• if number of commas is 4 that means that it has 5 values. Add desired string at the beginning, then use SUBSTRING from 1 to last comma

---

SQL Server version for comparison:

DECLARE @val VARCHAR(100) = 'cherry';

UPDATE mytable
SET words = CASE 
            WHEN LEN(words)-LEN(REPLACE(words, ',', '')) < 4 THEN @val + ', ' + words
            ELSE LEFT(@val + ', ' + words,
                      LEN(@val + ', ' + words) - CHARINDEX(',', REVERSE(words)))
            END
WHERE words NOT LIKE '%'+ @val  +'%';

SELECT * FROM mytable;

LiveDemo