Reputation: 26212
I'm updating data by selecting data from table and inserting into another. However there are some constraints on the other table and I get this :
DETAIL: Key (entry_id)=(391) is duplicated.
I basically do this :
insert into table_tmp
select * from table_one
How can I skip insert when this key entry duplicate occurs?
Update I can't save this schema info on SQL fiddle but here it is :
CREATE TABLE table1
("entry_id" int, "text" varchar(255))
;
INSERT INTO table1
("entry_id", "text")
VALUES
(1, 'one'),
(2, 'two'),
(3, 'test'),
(3, 'test'),
(12, 'three'),
(13, 'four')
;
CREATE TABLE table2
("entry_id" int, "text" varchar(255))
;
Create unique index entry_id_idxs
on table2 (entry_id)
where text='test';
INSERT INTO table2
("entry_id", "text")
VALUES
(1, 'one'),
(2, 'two'),
(3, 'test'),
(3, 'test'),
(12, 'three'),
(13, 'four')
;
Error that I get if I try to build the schema
Upvotes: 2
Views: 176
Reputation: 425328
Insert using join that returns unmatched rows:
INSERT INTO table2
SELECT DISTINCT t1.*
FROM table1 t1
LEFT JOIN table2 t2 ON t2.entry_id = t1.entry_id
WHERE t2.entry_id IS NULL
Upvotes: 2
Reputation: 116417
Use this query - SQLFiddle Demo:
INSERT INTO table2
SELECT t1.* FROM table1 t1
WHERE NOT EXISTS (
SELECT entry_id
FROM table2 t2
WHERE t2.entry_id = t1.entry_id
)
Upvotes: 2