MRM MRM - 4 months ago 10
MySQL Question

MySQL insert without duplicates from another table

I have two tables, an empty one and one with data. I want to insert into empty one data from the other, without duplicates.
Tables:

TABLE T1
AutoNo INT NOT NULL AUTO_INCREMENT,
p1 VARCHAR(20) NOT NULL,
p2 VARCHAR(20) NOT NULL,
PRIMARY KEY (AutoNo),
UNIQUE KEY `ppp` (p1,p2) //this one is empty, no records

TABLE T2
AutoNo INT NOT NULL AUTO_INCREMENT,
q1 DATE NOT NULL,
q2 TIME NOT NULL,
q3 VARCHAR(40) NOT NULL,
q4 VARCHAR(40) NOT NULL,
PRIMARY KEY (AutoNo)


What i need is to take columns q3 and q4, and put in p1 and p2, but the combination of q3 and q4 must not be a duplicate (first occurrence is fine to copy, the others are not welcomed).

I used:

INSERT INTO T1 (p1, p2)
SELECT q3, q4
FROM T2
LEFT JOIN T1 ON
T1.p1= T2.q3 AND
T1.p2= T2.q4
WHERE T1.p1 IS NULL AND T1.p2 IS NULL;


for this one i get an error code
#1062:Duplicate entry 'xxxx' for key q3
.

And i also tried:

INSERT INTO T1(p1, p2)
SELECT q3, q4
FROM T2
WHERE NOT EXISTS
(SELECT AutoNo FROM T1
WHERE T1.p1 = T2.q3 AND
T1.p2 = T2.q4);


and i get the same
#1062:Duplicate entry 'xxxx' for key q3
error.

Any help or suggestion would be much obliged.

Answer

I think you just want select distinct:

INSERT INTO T1(p1, p2)
    SELECT DISTINCT q3, q4
    FROM T2;

The NOT EXISTS version does not see the modified table. It only sees the empty table.

Although I prefer the above, an alternative is to use ON DUPLICATE KEY UPDATE:

INSERT INTO T1(p1, p2)
    SELECT q3, q4
    FROM T2
    ON DUPLICATE KEY UPDATE 13 = VALUES(13);

This clause is essentially a no-op -- nothing gets done but the error in the unique index is ignored.