General SQL Question (INSERT, Update and Delete)

Databases
1

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

really simple

INSERT INTO … ON DUPLICATE KEY UPDATE …

examples in da manual under INSERT syntax

:slight_smile:

3

In MySQL you can use this: http://dev.mysql.com/doc/refman/5.1/en/insert-on-duplicate.html

In other databases I don’t know.

4

guido, thanks for the correction, i overlooked that this was posted in the database forum and not the mysql forum

5

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

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? :slight_smile:

p.s. yes, ON DUPLICATE KEY UPDATE syntax works only in MySQL

7

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

that ~was~ pseudocode :wink:

as for any real code, it’s been over a decade since i touched asp, and i forgot the codes, too

9

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.