SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    Keep it simple, stupid! bokehman's Avatar
    Join Date
    Jul 2005
    Posts
    1,935
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    ON DUPLICATE KEY question

    My table has col1 and col2 as a joint PRIMARY KEY.

    Code:
    INSERT INTO mytable ( col1, col2)
    VALUES ('val1', 'val2'), ('val3', 'val4'), ('val5', 'val6')
    How do I update col2 to the new values if the insert fails. If I use ON DUPLICATE KEY UPDATE I can only set them all to the same value, not individual values.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    i believe ON DUPLCATE KEY UPDATE is not appropriate here

    you probably want to use INSERT IGNORE so that if you do attempt to add a {col1,col2} pair that already exists, the insert statement continues without failing
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard bronze trophy Immerse's Avatar
    Join Date
    Mar 2006
    Location
    Netherlands
    Posts
    1,661
    Mentioned
    7 Post(s)
    Tagged
    1 Thread(s)
    Isn't this a case for REPLACE INTO? Or am I misunderstanding the question?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Immerse View Post
    Isn't this a case for REPLACE INTO? Or am I misunderstanding the question?
    i think you understood fine

    this appears to be a relationship table (two-column PK, each probably an FK)

    REPLACE INTO will delete the row first, before inserting the new one

    INSERT IGNORE doesn't do either, and so is more efficient here

    but this is due entirely to the fact that there are no data (non-key) columns in the table

    add a datetime_added column, though, and then it's between REPLACE INTO and INSERT ON DUPLICATE KEY UPDATE, and i prefer the latter because a single update is more efficient than a delete and insert

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Keep it simple, stupid! bokehman's Avatar
    Join Date
    Jul 2005
    Posts
    1,935
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What does REPLACE INTO do if the row doesn't exist?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by bokehman View Post
    What does REPLACE INTO do if the row doesn't exist?
    insert



    Quote Originally Posted by da manual
    REPLACE is a MySQL extension to the SQL standard. It either inserts, or deletes and inserts. For another MySQL extension to standard SQL — that either inserts or updates — see Section 12.2.4.3, “INSERT ... ON DUPLICATE KEY UPDATE Syntax”.
    da manual... i haz it

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •