SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Enthusiast
    Join Date
    Dec 2004
    Location
    delaware
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    General SQL Question (INSERT, Update and Delete)

    Guys, I think I've asked this question in the past but got pulled away and never got back to it, but now I have some more time. My question has to do with general SQL usage.

    1) Let's say I have a database with a list of computers and some fields such as computerName(PK), customerName, phone, and so on.

    2) Let's say I have a folder where a file or files may exist named computerName.txt. In that file(s) will be the fields I have above. The file may be a new name not yet in the database or it may be an existing name with some of the fields having updated values. After I finish with the file I delete it. I then check back again hourly to see if there are any new files in the folder.

    3) Now I want to scan that folder, read each file, and put it into the database.

    My problem is that all the tutorials I have seen for INSERT assume there are no entries with the PK already in the database. How do I check for the record and if it's in the database check the fields to see if any of the values have changed and UPDATE them? If the record does not exist then INSERT the new record into the database.

    I imagine this is done with subqueries right? Can you guys give me some psuedo to show me how it's done? Thanks.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by notta View Post
    How do I check for the record and if it's in the database check the fields to see if any of the values have changed and UPDATE them?
    really simple

    INSERT INTO ... ON DUPLICATE KEY UPDATE ...

    examples in da manual under INSERT syntax

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

  3. #3
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    In MySQL you can use this: http://dev.mysql.com/doc/refman/5.1/...duplicate.html

    In other databases I don't know.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    guido, thanks for the correction, i overlooked that this was posted in the database forum and not the mysql forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Dec 2004
    Location
    delaware
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the replies guys. I never knew ON DUPLICATE KEY UPDATE existed. Does that exist in Microsoft SQL and is that normally how things are updated and inserted?

    You see right now I have a Microsoft SQL 2005 Express database that came with a product that we purchased that holds information about the computers we support. I then have a bunch of files that hold customer information that the commercial product does not have. I want to get the file data into a database but I've never used INSERT, UPDATE, DELETE in real world scenarios before. I use Vbscript to do SELECT statements from the MS database everyday, but now I want to get the file data into another database which probably will be MYSQL.

    If I stick with Microsoft SQL since the SQL server is already in place, back to my original question, how would you handle what I described originally?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by notta View Post
    ...how would you handle what I described originally?
    me?

    i would do the INSERT, and check the return code

    if the return code indicates success, i'm done

    if the return code indicates a database failure, i print error message

    otherwise, return code is "duplicate key" and then i know that the row already exists, so i do the UPDATE

    notice that there is no race condition this way

    most people, without much thought, do a SELECT first, and then, if it returns 0 rows, they try to do the INSERT -- race condition

    there is also another consideration, server utilization

    let's say that you only occasionally expect to find a dupe, but most of the time, the row doesn't exist yet

    this means that if you do the SELECT and then the INSERT, you're basically running two queries most of the time

    my method, on the other hand, basically runs only one query, and only occasionally a second one

    neat, eh?

    p.s. yes, ON DUPLICATE KEY UPDATE syntax works only in MySQL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Enthusiast
    Join Date
    Dec 2004
    Location
    delaware
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i would do the INSERT, and check the return code

    if the return code indicates success, i'm done

    if the return code indicates a database failure, i print error message

    otherwise, return code is "duplicate key" and then i know that the row already exists, so i do the UPDATE
    Thanks man. Could you show me some psuedo for that or an actual example?

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    that ~was~ pseudocode

    as for any real code, it's been over a decade since i touched asp, and i forgot the codes, too
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,633
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    For MSSQL, best bet is to either make a stored proc which handles the "INSERT OR UPDATE" logic, or do it in your client app with a multi-line SQL statement. This will keep it inside a transaction which should avoid the race condition in general.


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
  •