Suragch Suragch - 7 months ago 19
SQL Question

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

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

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

Comments