Inserting rows without adding duplicates...

[Jim R]

I'm trying to add rows to one table (wp_terms) with data from another (a_players) without adding duplicate data. 

 

I searched the Googles and ChatGPT, both effectively said:

on duplicate key  update column1 = column1, column2 = column2

 

It's not stopping duplicates...

insert into wp_terms (name,slug)

select concat(nameFirst,' ',nameLast),lower(concat(nameFirst,'-',nameLast))
from a_players
on duplicate key update name = name, slug = slug

 

I've also tried INSERT IGNORE and WHERE NOT EXISTS

[Barand]

For "ON DUPLICATE KEY" to work, mysql needs to know what column(s) values must be unique.

You do this by defining a UNIQUE KEY on that/those columns

[Jim R]

I believe it's set up.  

 

[Barand]

post outout from this query...

SHOW CREATE TABLE wp_terms

 

[Jim R]

I know it has unique fields set up for name and slug.  I think cross checking both.  

[Jim R]

[Barand]

Your UNIQUE key is the combination of (slug, tag_check). NULL values are ignored and your insert query always writes NULL to tag_check column.

CREATE TABLE `dupe_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `slug` varchar(50) NOT NULL DEFAULT '',
  `tag_check` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `player_tag_check` (`slug`,`tag_check`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

 

select * from dupe_test;

+----+-----------+-----------+
| id | slug      | tag_check |
+----+-----------+-----------+
|  1 | hugh-jass |      NULL |
|  2 | hugh-jass |      NULL |
|  3 | hugh-jass |      NULL |
|  4 | hugh-jass |         1 |
+----+-----------+-----------+

Add another...

MariaDB [test]> insert into dupe_test (slug) values ('hugh-jass');
Query OK, 1 row affected (0.051 sec)

MariaDB [test]> select * from dupe_test;
+----+-----------+-----------+
| id | slug      | tag_check |
+----+-----------+-----------+
|  1 | hugh-jass |      NULL |
|  2 | hugh-jass |      NULL |
|  3 | hugh-jass |      NULL |
|  6 | hugh-jass |      NULL |  << NEW
|  4 | hugh-jass |         1 |
+----+-----------+-----------+
5 rows in set (0.000 sec)

Now try adding again but with tag_check = 1 then 2...

MariaDB [test]> insert into dupe_test (slug, tag_check) values ('hugh-jass', 1);
ERROR 1062 (23000): Duplicate entry 'hugh-jass-1' for key 'player_tag_check'

MariaDB [test]> insert into dupe_test (slug, tag_check) values ('hugh-jass', 2);
Query OK, 1 row affected (0.073 sec)

MariaDB [test]> select * from dupe_test;
+----+-----------+-----------+
| id | slug      | tag_check |
+----+-----------+-----------+
|  1 | hugh-jass |      NULL |
|  2 | hugh-jass |      NULL |
|  3 | hugh-jass |      NULL |
|  6 | hugh-jass |      NULL |
|  4 | hugh-jass |         1 |
|  8 | hugh-jass |         2 |
+----+-----------+-----------+
6 rows in set (0.000 sec)

So either

• insert a tag_check value too, or
• exclude tag_check from the UNIQUE key

[Jim R]

How do I work that into my query?  

insert into wp_terms (name,slug,tag_check)

select concat(nameFirst,' ',nameLast),lower(concat(nameFirst,'-',nameLast)),1
from a_players
on duplicate key update slug = slug, tag_check = tag_check

This got me closer, but it still created duplicates for those who have been tagged yet.  

[Barand]

Is it necessary to include the tag_check in the unique key?

Would this be acceptable...

+----+-----------+-----------+
| id | slug      | tag_check |
+----+-----------+-----------+
|  1 | hugh-jass |      1    |
|  2 | hugh-jass |      2    |
+----+-----------+-----------+

or can there be only one "hugh-jass" in the table?

[Jim R]

In theory, yes, and for this process more than fine.  However, over time there are some duplicate names who come along later that I have to address.  

For those instance, we can assume there can only be one Hugh Jass.  

 

I'm trying to add bulk terms into WordPress' terms table from my players database.  (I could just query the list and do it via the front end, but I don't know if it strain the system.   It also seems like something to learn how to do directly into the database.)

[Jim R]

This worked for me:

insert into wp_terms (name,slug)

select concat(nameFirst,' ',nameLast) as name,lower(concat(nameFirst,'-',nameLast))
from a_players
where not exists(
select 1
from wp_terms
where wp_terms.name = concat(a_players.nameFirst,' ',a_players.nameLast))

 

Helpful as always.  

Thank you. 

[Barand]

Earlier in this thread you said that this ...

+----+-----------+-----------+
| id | slug      | tag_check |
+----+-----------+-----------+
|  1 | hugh-jass |      1    |
|  2 | hugh-jass |      2    |
+----+-----------+-----------+

... was acceptable. Your "solution" would not permit this.

Secondly, you are using a "dependent subquery" which means for every record inserted you must query all the existing lookig for a duplicate. This can be extremely slow and should be avoided when writing queries.

Changing your unique key to ...

UNIQUE INDEX `unq_name` (`name`) USING BTREE,

... and using this query would have same result ...

INSERT IGNORE into wp_terms (name,slug)
SELECT concat(nameFirst,' ',nameLast)
     , lower(concat(nameFirst,'-',nameLast))
FROM a_players
;