INSERT ON DUPLICATE KEY UPDATE failes in postmeta table

Question

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.

Answer 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.