Clever Geek Handbook

Insert line only if line doesn't exist

I am creating a counter. I have an article directory and unique visitor tracking. When a visitor arrives, I insert the article ID and my IP address into the database. First I check if the ip exists for the article id, if the ip doesn't exist I do an insert. These are two requests - is there any way to make this one request

Also, I am not using stored procedures. I am using normal inline sql

+2


source to share


8 answers


Here are some options:

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

from the MySQL reference manual: "If you use the IGNORE keyword, errors that occur when executing an INSERT statement are handled instead. For example, without IGNORE, a row that duplicates an existing UNIQUE index or a PRIMARY KEY value in a table raises a duplicate key error, and the statement aborts. ".) If the entry does not already exist, it will be created.



Another variant:

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

Doesn't throw errors or warnings.

+3


source


Yes, you are creating a UNIQUE constraint on the article_id and ip_address columns. When you try to INSERT a duplicate, the INSERT will fail with an error. Just answered the same question here for SQLite.



+2


source 


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


source


Not with SQL Server. With T-SQL, you need to check for the existence of a row, then use both INSERT and UPDATE.

Another option is to try UPDATE first and then check the row count to see if the record has been updated. If not, then INSERT. Given a 50/50 chance of having a row, you have completed one query 50% of the time.

MySQL has an extension called REPLACE which has the capability you are looking for.

+1


source


The only way I can think of is to execute dynamic SQL using an object SqlCommand

.

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


source


I agree with Larry on the use of uniqueness, but I would implement it like this:

  • IP_ADDRESS

    , pk
  • ARTICLE_ID

    , pk, fk

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

0


source


I would really use procedures! :)

But it will probably work anyway:

Create UNIQUE index on IP and Article columns, insert query will fail if they already exist, so technically it will work! (tested on mysql)

0


source


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)
0


source






More articles:


All Articles