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.
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.
Share Improve this question edited Jul 26, 2020 at 19:29 chithra asked Jul 26, 2020 at 17:43 chithrachithra 1336 bronze badges 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 Commented Jul 26, 2020 at 17:45
- @mozboz Updated the question. – chithra Commented Jul 26, 2020 at 17:49
1 Answer
Reset to default 1By 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.