Update table from SQLite3 queries results.
(1) By Nehemiah Narzary (nehemiahnarzary) on 2021-05-26 13:50:32 [link]
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
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.