INSERT or UPDATE?

A question from comp.lang.php:

I have a MySQL table of pages each with a ‘title’ and ‘page’ fields.

How do I do either an UPDATE if the pages ‘title’ exists or an INSERT if it does not?

Assuming that `title` is a primary or unique key, it’s very simple:

INSERT INTO `pages` (`title`, `page`)
  VALUES ('My Title', 'My Page')
  ON DUPLICATE KEY UPDATE `page`='My Page'

See MySQL documentation for more information:

http://dev.mysql.com/doc/refman/4.1/en/insert.html

Alternatively, you can issue a REPLACE query:

http://dev.mysql.com/doc/refman/4.1/en/replace.html

The difference is that with INSERT … ON DUPLICATE KEY UPDATE, you can update an existing record partially (i.e., if there are fields other than `title` and `page`, they will be kept intact). REPLACE, however, deletes an existing row (if it exists, that is) before writing a new one, so any fields not explicitly set by the REPLACE query will be set to their default values.

This entry was posted in Answers, Technology. Bookmark the permalink.

Leave a Reply

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