Duplicating SQLite Rows into same table with one updated field?

Issue

So I have a db that contains contacts. Each contact has a folder field that I use to categorize them when displaying in the app, my id is a combo of unique id from server and folder type. What I would like to do is take every contact in my table and duplicate them 1 time with an updated folder value of “all”.

So if I only had 2 contacts if would look something like this before the INSERT

---------------------------------------------------
id         |   name |   folder |   area |  number |
---------------------------------------------------
1abfav     |  John  |   fav    |   111  | 1234567 |
2cdarchive |  Susan |  archive |   111  | 6785678 |

And After the insert I would have

---------------------------------------------------
id         |   name |   folder |   area |  number |
---------------------------------------------------
1abfav     |  John  |   fav    |   111  | 1234567 |
2cdarchive |  Susan |  archive |   111  | 6785678 |
1aball     |  John  |   all    |   111  | 1234567 |
2cdall     |  Susan |   all    |   111  | 6785678 |

Solution

With INSERT INTO ... SELECT:

insert into contacts(id,name,folder,area,number)
select 
  substr(id, 1, length(id) - length(folder)) || 'all',
  name,
  'all',
  area,
  number
from contacts;

See the demo.
Results:

| id         | name  | folder  | area | number  |
| ---------- | ----- | ------- | ---- | ------- |
| 1abfav     | John  | fav     | 111  | 1234567 |
| 2cdarchive | Susan | archive | 111  | 6785678 |
| 1aball     | John  | all     | 111  | 1234567 |
| 2cdall     | Susan | all     | 111  | 6785678 |

Answered By – forpas

Answer Checked By – Timothy Miller (FlutterFixes Admin)