Fill column based on other columns- SQLITE

Question

I am interested in performing:

Insert values to a new column, based on values in a different column (same table) for example: original column- productName original column- store_id target column- final

in this case, I wish to insert in "final" a product name shortcut, concatenated with the store_id.

this is an example of the end result-

productName	store_id	final
phone	001	pho_001
phone	014	pho_014
truck	022	tru_022
guitar	022	tar_022
phone	004	pho_004

I wish to create conditions- if the productName is 'phone'- insert 'pho' along with the store id. if the product name is 'truck' insert 'tru' along with the store id and so on. there is a desecrate type of products (about 9) so conditions could be applied for final name.

the table is large, and I am looking for a solution that would apply to all rows. not inserting one by one.

note- the shortcut in final is not necessarily built from the first 3 letters of the product

thank you!

Answer 1

Use a CASE expression in the UPDATE statement to concatenate what you want:

UPDATE tablename
SET final = CASE productname
              WHEN 'phone' THEN 'pho'
              WHEN 'truck' THEN 'tru'   
              WHEN 'guitar' THEN 'tar'
              WHEN .....
              ELSE .....
            END || '_' || store_id;