CODEWITHSUNDEEP
c#sqlsql-server-2005
Luke101
Luke101

Reputation: 65238

Insert a Row Only if a Row does not Exist

I am building a hit counter. I have an article directory and tracking unique visitors. When a visitor comes i insert the article id and their IP address in the database. First I check to see if the ip exists for the article id, if the ip does not exist I make the insert. This is two queries -- is there a way to make this one query

Also, I am not using stored procedures I am using regular inline sql

Upvotes: 2

Views: 2364

Answers (8)

gbn
gbn

Reputation: 432190

IF NOT EXISTS (SELECT * FROM MyTable where IPAddress...)
   INSERT...

Upvotes: 2

Charles Bretana
Charles Bretana

Reputation: 146409

try this (it's a real kludge, but it should work...):

Insert TableName ([column list])
Select Distinct @PK, @valueA, @ValueB, etc. -- list all values to be inserted
From TableName
Where Not Exists 
    (Select * From TableName
     Where PK == @PK)

Upvotes: 0

Matt Brunell
Matt Brunell

Reputation: 10389

Not with SQL Server. With T-SQL you have to check for the existence of a row, then use either INSERT or UPDATE as appropriate.

Another option is to try UPDATE first, and then examine the row count to see if there was a record updated. If not, then INSERT. Given a 50/50 chance of a row being there, you have executed a single query 50% of the time.

MySQL has a extension called REPLACE that has the capability that you seek.

Upvotes: 1

junmats
junmats

Reputation: 1924

Here are some options:

 INSERT IGNORE INTO `yourTable`
  SET `yourField` = 'yourValue',
  `yourOtherField` = 'yourOtherValue';

from MySQL reference manual: "If you use the IGNORE keyword, errors that occur while executing the INSERT statement are treated as warnings instead. For example, without IGNORE, a row that duplicates an existing UNIQUE index or PRIMARY KEY value in the table causes a duplicate-key error and the statement is aborted.".) If the record doesn't yet exist, it will be created.

Another option would be:

INSERT INTO yourTable (yourfield,yourOtherField) VALUES ('yourValue','yourOtherValue')
ON DUPLICATE KEY UPDATE yourField = yourField;

Doesn't throw error or warning.

Upvotes: 3

Alex Weber
Alex Weber

Reputation: 2186

I would really use procedures! :)

But either way, this will probably work:

Create a UNIQUE index for both the IP and article ID columns, the insert query will fail if they already exist, so technically it'll work! (tested on mysql)

Upvotes: 0

OMG Ponies
OMG Ponies

Reputation: 332521

I agree with Larry about using uniqueness, but I would implement it like this:

  • IP_ADDRESS, pk
  • ARTICLE_ID, pk, fk

This ensures that a record is unique hit. Attempts to insert duplicates would get an error from the database.

Upvotes: 0

Shankar R10N
Shankar R10N

Reputation: 4966

The only way I can think of is execute dynamic SQL using the SqlCommand object.

IF EXISTS(SELECT 1 FROM IPTable where IpAddr=<ipaddr>)
--Insert Statement

Upvotes: 0

Larry Lustig
Larry Lustig

Reputation: 50970

Yes, you create a UNIQUE constraint on the columns article_id and ip_address. When you attempt to INSERT a duplicate the INSERT will be refused with an error. Just answered the same question here for SQLite.

Upvotes: 2

Related Questions