Insert a Row Only if a Row does not Exist

Question

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

Answer 1

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)

Answer 2

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

Answer 3

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.

Answer 4

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.

Answer 5

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.

Answer 6

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