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