SQLite Copy Table Content from one Table to another Table and Update

user_18449045 0
Viewed 57

I have two SQLite files, each of them has one table and the same table design. One Column is set as Primary Key. I want to copy all data from ItemsB into ItemsA. All data should be updated. The ItemsB Table is the newer one.

I've tried:

ATTACH DATABASE ItemsB AS ItemsB;
INSERT INTO ItemsA.PMItem (ItemID,VarID,Name1) SELECT ItemID,VarID,Name1 FROM ItemsB.PMItem;

Obviously this can't work due the Primary Key (which is the column VarID).

Then I tried it with ON CONFLICT:

ON CONFLICT (VarID) DO UPDATE SET Name1=excluded.Name1

But this won't work either.

Example Table:

CREATE TABLE PMItem (
    ItemID INTEGER,
    VarID  INTEGER PRIMARY KEY,
    Name1  TEXT
);
sql-insert upsert sql-update sqlite
1 Answers

You need a WHERE clause with an always true condition, to overcome the ambiguity that is raised when ON CONFLICT is used after a SELECT statement:

INSERT INTO PMItem (ItemID,VarID,Name1) 
SELECT ItemID,VarID,Name1 
FROM ItemsB.PMItem
WHERE 1
ON CONFLICT(VarID) DO UPDATE 
SET Name1 = EXCLUDED.Name1;
user_104988280
Related