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

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
);

CodePudding user response：

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;