1

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.

2
  • Probably you haven't got a row with that key then if it's INSERTing a new one. Please add the full SQL you're using to your question.
    – mozboz
    Jul 26 '20 at 17:45
  • @mozboz Updated the question.
    – chithra
    Jul 26 '20 at 17:49
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.

8
  • Can we do it with post_id, meta_key combination? I've created a unique key combining post_id and meta_key. But it works just the same.
    – chithra
    Jul 26 '20 at 17:58
  • heh yes I just updated the answer with that as a suggestion.
    – mozboz
    Jul 26 '20 at 18:04
  • It works for me, but please note you need to specify a limited length for the UNIQUE index - I'll update the answer
    – mozboz
    Jul 26 '20 at 18:12
  • Thanks. I checked this again. I have an index meta_key_post_id in the table. But it inserts new rows again and again with the given SQL query.
    – chithra
    Jul 26 '20 at 18:21
  • 1
    You need to create the key as per my answer. When it's correct it will look something like this in CREATE TABLE: UNIQUE KEY `post_metakey_index` (`post_id`,`meta_key`(100)),
    – mozboz
    Jul 27 '20 at 8:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.