[FIXED] How to add a list to DB Browser for SQLite

Issue

I have data that follows:

vocab   phonetictranscription_bre   phonetictranscription_ame
little           ˈlɪtl                       ˈlɪtəl
settle           ˈsɛtl                       ˈsɛtəl
...

If the word is in the list below, I want to change every 't' symbol in phonetictranscription_ame with the 'd' symbol

['little', 'better', 'pretty', 'matter', 'letter', 'committee', 'pattern', 'battle', 'attitude', 'settle', 'bottom', ...]

So I thought it could be done with a for loop like:

for element in list:
     UPDATE table_name
     SET phonetic = REPLACE (phonetic, 't', 'd')
     WHERE vocab = element

would work but I couldn't find any information regarding creating a list on SQLite DB browser. I couldn't find anything online either.

Expected outcome:

vocab   phonetictranscription_bre   phonetictranscription_ame
little           ˈlɪtl                       ˈlɪdəl
settle           ˈsɛtl                       ˈsɛdəl
...

Solution

You can create a CTE that returns the list and use in the WHERE clause of the UPDATE statement:

WITH cte(vocab) AS (VALUES
 ('little'), ('better'), ('pretty'), ('matter'), ('letter'), ('committee'), 
 ('pattern'), ('battle'), ('attitude'), ('settle'), ('bottom') 
)
UPDATE table_name AS t
SET phonetictranscription_ame = REPLACE(t.phonetictranscription_ame, 't', 'd')
WHERE t.vocab IN cte;

See the demo.

Answered By - forpas

Answer Checked By - Senaida (PHPFixing Volunteer)