Prevent duplicate entries while inserting through other tables

Question

I need to extract some column values from T2 and insert them into T3 by looping through T1. And ofcourse I must avoid the duplicate entries in T3.

My tables:

CREATE TABLE T1 (
    Id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (Id)
);

CREATE TABLE T2 (
    Id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    FK_T1 BIGINT(20) UNSIGNED NOT NULL,
    Desc VARCHAR(10) NOT NULL,
    PRIMARY KEY (Id),
    UNIQUE KEY FK_T1 (FK_T1),
    CONSTRAINT FK_T2_T1 FOREIGN KEY (FK_T1) REFERENCES T1 (Id)
);

CREATE TABLE T3 (
    Id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    FK_T1 BIGINT(20) UNSIGNED NOT NULL,
    T2Desc VARCHAR(10) NOT NULL,
    PRIMARY KEY (Id),
    UNIQUE KEY FK_T1 (FK_T1),
    CONSTRAINT FK_T3_T1 FOREIGN KEY (FK_T1) REFERENCES T1 (Id)
);

Data:

T1
+----+
| Id |
+----+
| 11 |
| 12 |
+----+

T2
+----+-------+--------+
| Id | FK_T1 | Desc   |
+----+-------+--------+
| 21 | 11    | desc 1 |
| 22 | 12    | desc 2 |
+----+-------+--------+

T3
+----+-------+--------+
| Id | FK_T1 | T2Desc |
+----+-------+--------+
| xx | 11    | desc 1 |
+----+-------+--------+

I would expect a new row to be added into T3 when I execute the following query but it gives Duplicate entry '11' for key 'FK_T1' error ofcourse:

-- expected new row to be added => | xx | 12 | desc 2 |

INSERT INTO T3(FK_T1, T2Desc)  (
    SELECT t2.FK_T1, t2.Desc
    FROM T2 t2
    INNER JOIN T1 t1 ON t1.Id = t2.FK_T1
);

How can I make this query work with a clear extension? May be by using LEFT JOIN so that I can even also filter the T1 first with something like WHERE T1.Id > 10?

Answer 1

You can also use this Query without INGORE. This only selects the rows that are not in table3 and insert.

INSERT INTO T3 (FK_T1, T2Desc)
SELECT T2.FK_T1, t2.Desc
FROM T2 T2 
JOIN T1 T1 ON T1.Id = T2.FK_T1
LEFT JOIN T3 T3 ON T2.FK_t1 = T3.FK_T1 
WHERE T3.FK_T1 IS NULL;

SAMPLE see fiddle

CREATE TABLE T1 (
    Id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (Id)
);
CREATE TABLE T2 (
    Id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    FK_T1 BIGINT(20) UNSIGNED NOT NULL,
    `Desc` VARCHAR(10) NOT NULL,
    PRIMARY KEY (Id),
    UNIQUE KEY FK_T1 (FK_T1),
    CONSTRAINT FK_T2_T1 FOREIGN KEY (FK_T1) REFERENCES T1 (Id)
);
CREATE TABLE T3 (
    Id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    FK_T1 BIGINT(20) UNSIGNED NOT NULL,
    T2Desc VARCHAR(10) NOT NULL,
    PRIMARY KEY (Id),
    UNIQUE KEY FK_T1 (FK_T1),
    CONSTRAINT FK_T3_T1 FOREIGN KEY (FK_T1) REFERENCES T1 (Id)
);

add Rows

INSERT INTO T1 VALUES (11),(12);
INSERT INTO T2 VALUES (21, 11, 'desc 1'), (22, 12, 'desc 2');
INSERT INTO T3 VALUES (31, 11, 'desc 1');

Update T3

mysql> INSERT INTO T3 (FK_T1, T2Desc)
    -> SELECT T2.FK_T1, T2.Desc
    -> FROM T2 T2
    -> JOIN T1 T1 ON T1.Id = T2.FK_T1
    -> LEFT JOIN T3 T3 ON T2.FK_t1 = T3.FK_T1
    -> WHERE T3.FK_T1 IS NULL;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

see Result

mysql> SELECT * FROM T3;
+----+-------+--------+
| Id | FK_T1 | T2Desc |
+----+-------+--------+
| 31 |    11 | desc 1 |
| 32 |    12 | desc 2 |
+----+-------+--------+
2 rows in set (0.00 sec)

mysql>

insert new row and test

mysql> INSERT INTO T1 VALUES (33),(34);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO T2 VALUES (41, 33, 'desc 33'), (51, 34, 'desc 34');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql>


mysql> INSERT INTO T3 (FK_T1, T2Desc)
    -> SELECT T2.FK_T1, T2.Desc
    -> FROM T2 T2
    -> JOIN T1 T1 ON T1.Id = T2.FK_T1
    -> LEFT JOIN T3 T3 ON T2.FK_t1 = T3.FK_T1
    -> WHERE T3.FK_T1 IS NULL;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM T3;
+----+-------+---------+
| Id | FK_T1 | T2Desc  |
+----+-------+---------+
| 31 |    11 | desc 1  |
| 32 |    12 | desc 2  |
| 33 |    33 | desc 33 |
| 34 |    34 | desc 34 |
+----+-------+---------+
4 rows in set (0.00 sec)

try query again without tablechanges

mysql> INSERT INTO T3 (FK_T1, T2Desc)
    -> SELECT T2.FK_T1, T2.Desc
    -> FROM T2 T2
    -> JOIN T1 T1 ON T1.Id = T2.FK_T1
    -> LEFT JOIN T3 T3 ON T2.FK_t1 = T3.FK_T1
    -> WHERE T3.FK_T1 IS NULL;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>

Answer 2

Using INSERT IGNORE can be expensive, especially if the table has a lot of rows or the data which you want to insert contains a lot of duplicates.

So why don't keep things simple and filter duplicates already in your select statement?

INSERT INTO T3 (FK_T1, T2Desc)
SELECT t2.FK_T1, t2.Desc1 FROM T2 t2 JOIN T3 t3 
  ON t2.FK_t1 != t3.FK_T1 
INNER JOIN T1 t1 ON t1.Id = t2.FK_T1

Answer 3

Test

INSERT IGNORE INTO T3 (FK_T1, T2Desc)
SELECT T2.FK_T1, T2.`Desc`
FROM T1
JOIN T2 ON T1.Id = T2.FK_T1;

fiddle

PS. It is a bad idea to use reserved word Desc as a fieldname.