SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    Feb 2007
    Location
    Ohio, United States
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    PDO::lastInsertId() and INSERT... ON DUPLICATE KEY UPDATE

    Hello!

    In an application I'm writing, I use a INSERT... ON DUPLICATE KEY UPDATE query, through the PDO class. In the rest of my script I need the id of the row that was inserted or updated.

    I tried to use PDO::lastInsertId() to get the id, but if they query updates, the next auto increment id is returned instead of the updated row's id.

    In search for more information on the topic, I found this comment on the PHP Manual.

    Basically what this guy said is that the query "SELECT LAST_INSERT_ID()" return the same result as I'm getting, but the PHP function mysql_insert_id() returns the id you'd expect. I figure PDO probably uses the first method to find the last insert id.

    I have enough data that I could find the unique row's id again through another SELECT query, but I really would like to avoid superfluous queries. Is there any alternative?

    Thank you for your help!

    Edit:

    The MySQL Manual says to use the following statement:

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

    Wouldn't that update the row to adopt the next id? I need to preserve my ids, because there are references to them in other tables.

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    there's no way to get the id of the row that was updated without an additional SELECT statement when using INSERT... ON DUPLICATE KEY UPDATE.

    will you ever be doing bulk inserts? for example: INSERT INTO table (a,b,c) VALUES (1,2,3), (3,4,5), (5,6,7)
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  3. #3
    SitePoint Member
    Join Date
    Feb 2007
    Location
    Ohio, United States
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay, thanks for the answer.

    I've actually never used bulk inserts. This is a script I could really benefit from using them though…

  4. #4
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    this may not apply, but this is a really efficient way of doing a mass insert/update:
    1. create a temporary table
    2. load all of the rows to be updated/inserted in to the temporary table
    3. UPDATE the target table using a JOIN
    4. DELETE the rows from the temporary table that match the rows in the target table
    5. INSERT ... SELECT from the temporary table to the target table
    6. DROP the temporary table

    this will be much faster for doing bulk combination INSERT/UPDATES.

    you might also look at the REPLACE command if you are updating all of the columns except for the primary key.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  5. #5
    SitePoint Member
    Join Date
    Feb 2007
    Location
    Ohio, United States
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by longneck View Post
    this may not apply, but this is a really efficient way of doing a mass insert/update:
    1. create a temporary table
    2. load all of the rows to be updated/inserted in to the temporary table
    3. UPDATE the target table using a JOIN
    4. DELETE the rows from the temporary table that match the rows in the target table
    5. INSERT ... SELECT from the temporary table to the target table
    6. DROP the temporary table

    this will be much faster for doing bulk combination INSERT/UPDATES.

    you might also look at the REPLACE command if you are updating all of the columns except for the primary key.
    How much of an efficiency boost are we talking about? I only have around 7,000 rows. That method seems like a lot of work, and I don't see the logic behind why it'd be more efficient. I considered opting for a LOAD DATA INFILE statement, but I ended up just bulk inserting the rows 100 at a time.

    In a little bit when I write more of the script, I'm going to be inserting or updating over 112,000 rows. I'm considering using a REPLACE statement, but I'm not sure about efficiency for that many rows.

    All this isn't related to my original question, but help would be appreciated.


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •