SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast
    Join Date
    Jun 2005
    Posts
    66
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    update if present else insert

    i have the table tblCandidate:

    id (PK), candidateCode, DBName, Surname, Forename, ... etc

    Is it possible, with 1 sql command, to do the following?:

    I have a new entry, i input it to the table, iff there exists another entry with the same candidateCode AND DBName then this new entry will overwrite the old one. However, if no entry with the same candidateCode AND DBName exist then then this should be added as a new entry.

    I would have thought this would be possible in 1 command, but im not sure how to do it. Thanks for any feedback
    Rascal, am I? Take that!

    -Errol Flynn

  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)
    you want REPLACE. http://dev.mysql.com/doc/mysql/en/replace.html

    make candidateCode and DBName a unique index (as i showed you in your previous post) then use REPLACE.

    you could also use this form of insert from http://dev.mysql.com/doc/mysql/en/insert.html

    Code:
    INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
        [INTO] tbl_name
        SET col_name={expr | DEFAULT}, ...
        [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]
    the key difference is that REPLACE deletes the row, while INSERT ... ON DUPLICATE KEY UPDATE acutally updates. this distinction is important because if you're inserting (or replacing) and not specifying all of the non-auto_increment columns, then with REPLACE they will be set to the column defaults, while with the INSERT they will retain their old values.

  3. #3
    SitePoint Enthusiast
    Join Date
    Jun 2005
    Posts
    66
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Once again, thanks for your help LN, but if i could just ask for a little more of your patience. I've tried:
    Code:
    INSERT tblCandidate
        SET CandidateCode = 9, Name = "Chris.Berry", DBName = "YTPDB.mdb"
        ON DUPLICATE KEY UPDATE Name = "Chris.Berry"
    and
    Code:
    INSERT INTO tblCandidate (CandidateCode, Name, DBName)
    VALUES (9, "Chris.Berry", "YTPDB.mdb")
     ON DUPLICATE KEY UPDATE Name = "Chris.Berry"
    Neither works, i get sql error. As far as i can see it is an implementation of what you suggest, other examples ive found online look similar.

    I ran phpinfo(), and got this output about MySQL:

    Client API version 3.23.58

    Does this mean I'm running MySQL v.3.23.58? (I've read the heading post: "If your version is earlier than 4.1...please mention.... dont waste peoples time with solutions you cant implement." err.... sorry. I just presumed my host would be runnig most up-to-date version)

    I see the manual says ON DUPLICATE KEY is new as of v 4.1.0. Is this my problem? Maybe i should have a word with my host.....

    Cheers
    Last edited by chrisdb; Jun 21, 2005 at 01:31.
    Rascal, am I? Take that!

    -Errol Flynn

  4. #4
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    Plano
    Posts
    643
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    wow thats cool i've been using 1 DELETE query, followed by an INSERT query for the longest time now. all the features of mySQL never cease to amaze me. thanks, longneck

  5. #5
    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)
    Quote Originally Posted by chrisdb
    I see the manual says ON DUPLICATE KEY is new as of v 4.1.0. Is this my problem?
    yup


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
  •