MySQL insert without duplicates from another table

Question

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.

Forget T1 and the INSERT, how do you select all the data from T2 without duplicates? Once you can do that your problem is solved. — OGHaza
– OGHaza, Commented Aug 1, 2016 at 0:20
@Strawberry ignore is not accepted, errors will transform into warning, and i can't have neither at execute — MRM
– MRM, Commented Aug 1, 2016 at 0:33

Gordon Linoff · Accepted Answer · 2016-08-01 01:02:09Z

1

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.

edited Aug 1, 2016 at 1:02

answered Aug 1, 2016 at 0:36

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

MRM Over a year ago

For distinct i get Data truncated for column p2 at row ... and for on duplicate key an Unknown column q3 in field list

Gordon Linoff Over a year ago

@MRM . . . The truncation error is because the columns in the tables have different types. You can use left(q3, 20), left(q4, 20) if the intention is to take the first 20 characters.

MRM Over a year ago

Yep, you were right, i forgot to increase the number of varchars for each column. Now it work. Thank you very much!

Collectives™ on Stack Overflow

MySQL insert without duplicates from another table

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related