Update table from SQLite3 queries results.

(1) By Nehemiah Narzary (nehemiahnarzary) on 2021-05-26 13:50:32

I've a table with 2 columns (word and meaning). 
word column contains same entry in many rows so I want to make them together. 

This following query gives me good result but I can't update my table.


SELECT word, group_concat(meaning, '/') as meaning FROM Table1 GROUP BY word; 


This works for me. But the thing is I can't update it to my table.

(2) By Kees Nuyt (knu) on 2021-05-26 17:52:12 in reply to 1 [link]

It will be much easier to `INSERT` it in a new table than `UPDATE` one row in Table1 and `DELETE` the other rows. Something like:

```sql
CREATE TABLE Table2 (
  word TEXT PRIMARY KEY NOT NULL
, meaning TEXT
) WITHOUT ROWID;

INSERT INTO Table2 (word,meaning) 
 SELECT word, group_concat(meaning, ' / ') as meaning
 FROM Table1 GROUP BY word;
```

Now verify the contents of Table2. If all is well:

```sql
DROP TABLE Table1;
ALTER TABLE Table2 RENAME TO Table1;
```

(3) By Harald Hanche-Olsen (hanche) on 2021-05-27 17:44:30 in reply to 2 [link]

If there are views and triggers and whatnot attached to the table, that might not work so well. In which case, make Table2 temporary, and after populating it,

```
BEGIN;
DELETE FROM Table1;
INSERT INTO Table1 SELECT * FROM Table2;
END;
```

The transaction is just to recover in case of a crash or power loss.