Tag: duplicate key

2010-12-06

Insert On Duplicate Key Update and Last_Insert_ID()

by Charles — Categories: SQL — Tags: , , — Leave a comment

A useful MySQL trick I just learned tonight is using the “INSERT…ON DUPLICATE KEY UPDATE” query syntax along with the LAST_INSERT_ID() function in order to populate the mysql_insert_id() function or the mysqli->insert_id attribute when the record is a duplicate.

At first I had tried using the REPLACE syntax, thinking that would be easier. The problem was that the auto-increment primary key field values kept changing whenever I REPLACEd a duplicate unique value, which was surprising — to me. So then I read the manual and found out that…

REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.

That explained why my keys were changing, so I figured I’d have to use INSERT…ON DUPLICATE KEY UPDATE. Well, that didn’t seem to work, as I only got an insert_id value if it was a new entry. Amazingly enough, returning to the manual once again answered my question:

To make LAST_INSERT_ID() meaningful for updates, insert rows as follows:

INSERT INTO table (a,b,c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id), c=3;

So, now I can do what I wanted, which was to read some data in from a CSV file, insert the records while not getting an error if there is a duplicate unique field, and get the insert_id value to use in subsequent queries.

REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.

2009-12-10

Book List App: One Query to Rule Them All

by Charles — Categories: PHP, SQL — Tags: , , , — 1 Comment

I got side-tracked from working on this project for awhile, but I am trying to get back to it now.

One thing I needed to address was that for many of the database tables I would have situations where a user supplies some data, and if it already exists in the table then I just need the primary key for it, or else I need to insert it and then get the key. Typically I’ve seen people do something along the lines of a select query to see if it’s in there, and if not perform a second query to do the insert.

While thinking tonight about how ugly that is, it came to me that maybe I could do an INSERT…ON DUPLICATE KEY UPDATE query. What I wasn’t sure about was if you could get the insert ID if it already existed and therefore does an update. Upon reading the MySQL manual entry on it, it looked like it should work, but I still wasn’t positive the PHP mysql_insert_id() function would work with it, and even less sure the related CodeIgniter database functions would. After a bit of testing, the good news is, it works. This should save me doing extra queries in a number of places, plus eliminate any need for table locking (I think).

So the basic process is:

public function replace($lastName, $firstName)
{
   $lastName = $this->db->escape($lastName);
   $firstName = $this->db->escape($firstName);
   $sql = "
INSERT INTO `author` (`last_name`, `first_name`)
VALUES ($lastName, $firstName)
ON DUPLICATE KEY UPDATE `last_name` = $lastName
";
   $this->db->query($sql);
   if($this->db->affected_rows())
   {
      return $this->db->insert_id();
   }
   return false;
}

Hopefully this will save me — and maybe you — a bit of processing time and coding time.

© 2012 PHP Musings All rights reserved - Wallow theme v0.46.4 by ([][]) TwoBeers - Powered by WordPress - Have fun!