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.