We came to a situation where we needed to update a table with ~180 records with additional text. Like, every second record was supposed to contain the current value (string) plus append something else, so that if the original value was:
The quick brown fox jumps
we needed to append:
” over the lazy dog”
Luckily in SQL we have a conditional construction that serves as “IF” statement, for database management systems that would not support the IF THEN construction, and in SQLite its signature is like this:
CASE WHEN first conditional expression THEN column value
WHEN second conditional expression THEN column value
WHEN third conditional expression THEN column value
So what we basically want to do is this: get every item whose id field modulus with 2 is 0 (2, 4, 6 …) and append ” over the lazy dog” at the end.
So the SQLite syntax to append text to every second record in the table ‘tablename’ would be:
SET field = field ||
WHEN (ID % 2 = 0) THEN ' over the lazy dog'
Pay attention to the concatenation operator in SQLite. Here you can find more info about SQLite syntax.