MySQL–Update and Insert if not exists

Often you have the situation that you need to check if an table entry exists, before you can make an update. If it does not exist, you have to do an insert first.

The simple straightforward approach is this:

(The example is for an entry in the WordPress wp_postmeta table)

SELECT meta_id FROM wp_postmeta
WHERE post_id = ?id AND meta_key = ‘page_title’


If ResultCount == 0


INSERT INTO wp_postmeta (post_id, meta_key, meta_value)
VALUES (?id, ‘page_title’, ?page_title)


Else


UPDATE wp_postmeta SET meta_value = ?page_title
WHERE post_id = ?id AND meta_key = ‘page_title’

This is not too bad, but we could actually combine everything into one query. I found different solutions on the internet. The simplest, but MySQL only solution is this:

INSERT INTO users (username, email) VALUES (‘Jo’, ‘jo@email.com’)
ON DUPLICATE KEY UPDATE email =
‘jo@email.com’

Unfortunately, this the ‘ON DUPLICATE KEY’ statement only works on PRIMARY KEY and UNIQUE columns. Which is not a solution for my case.

You can follow the discussion here on Sql Recipes.

What worked best for me, was this solution:

INSERT INTO wp_postmeta (post_id, meta_key) SELECT ?id, ‘page_title’  FROM DUAL WHERE NOT EXISTS (SELECT meta_id FROM wp_postmeta WHERE post_id = ?id AND meta_key = ‘page_title’);
UPDATE wp_postmeta SET meta_value = ?page_title WHERE post_id = ?id AND meta_key = ‘page_title’;

It’s a bit more complex, but you can run that in one go and it will do the trick. If you adjust it a little it should even work on different database systems.




Ähnliche Beiträge


Ein Kommentar zu “MySQL–Update and Insert if not exists”

Leave a Reply

Your email address will not be published. Required fields are marked *



*