INSERT ON DUPLICATE KEY UPDATE failes in postmeta table

INSERT ON DUPLICATE KEY UPDATE Mysql query is not working with Postmeta table. It adds new/duplicate rows to the table. I couldn’t find a way to solve this. I need to use this as part of a bulk import script.

My query is:

INSERT INTO `wp_postmeta` (`post_id`, `meta_key`, `meta_value` ) 
VALUES (21100,"bygg_address","BILGATAN1 9 222B")
ON DUPLICATE KEY UPDATE `meta_value` = "BILGATAN1 92 B";

Even though I have a row with 21100, “bygg_address”, “BILGATAN1 9 222B”, it inserts a duplicate row instead of updating it.

SHOW CREATE TABLE OUTPUT:

CREATE TABLE `wp_postmeta` (
  `meta_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `post_id` bigint(20) unsigned NOT NULL DEFAULT '0',
  `meta_key` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `meta_value` longtext COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (`meta_id`),
  KEY `post_id` (`post_id`),
  KEY `meta_key` (`meta_key`(191)),
  KEY `meta_key_post_id` (`meta_key`(191),`post_id`)
) ENGINE=MyISAM AUTO_INCREMENT=221761 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Any help will be appreciated.

Contents hide

Answers:

Method 1

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

By doing a SHOW CREATE TABLE on wp_postmeta, I got the structure of that table which included this:

 PRIMARY KEY (`meta_id`),

So the key that MySQL can do the ‘insert or update’ on is the meta_id, which you’re not providing. Therefore in your query, it will always be added as a new row.

EDIT: The docs for INSERT ON DUPLICATE UPDATE say that it looks at either the PRIMARY KEY or any UNIQUE index, so you could possibly get around this by creating a new UNIQUE index on the combination of post_id and meta_key which might solve your problem.

WARNINGS

If this is a big table for you that could be a bad idea because it might slow down some read or write operations.
WordPress or some plugin might be relying on the ability to have duplicate rows wiht the same post_id and meta_key, and this would prevent those being inserted. It seems like it would be a bad idea for code to do this, but it’s perfectly possible.

Solution:

Tested this and it works for me:

ALTER TABLE wp_postmeta ADD UNIQUE `post_metakey_index` (post_id, meta_key(100));`

With this index, if I run the query from your question twice, it first inserts it, then updates it.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating