demonplus demonplus - 29 days ago 7
MySQL Question

Inserting rows in the same table in MySQL

I am doing the following

INSERT
query:

INSERT INTO Elements
(EventID, TypeID)
SELECT EventID, 2
FROM Elements
Where TypeID = 1


i.e. inserting the Elements rows in the same table with another TypeID. It works OK.

But the problem occurs in case some of that rows already exist with
TypeID = 2
. In this case I want just to skip
INSERT
for such rows and continue.

INSERT IGNORE
doesn't work for me because I don't have unique or something else for these columns. What can I do?

Answer

One option here is to add a unique index and then perform your inserts using INSERT IGNORE ... INTO:

ALTER TABLE Elements ADD UNIQUE unique_index (EventID, TypeID)

INSERT IGNORE INTO Elements (EventID, TypeID) 
SELECT EventID, 2
FROM Elements
WHERE TypeID = 1

This insert would fail if the EventID, TypeID pair of values already existed in the Elements table.