SQLite Forum

Update table from SQLite3 queries results.
Login

Update table from SQLite3 queries results.

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