CODEWITHSUNDEEP
mysqlsql
Ashley
Ashley

Reputation: 71

INSERT IF NOT EXIST

seems pretty simply but ive tried everything i can find on here and just keep getting an error #1064 - You have an error in your SQL syntax;

every time a user visits the page i want to update a table called range_views

INSERT INTO range_views (range_id, ip_address, view_date) values('112','123.123.123.123','2015-02-01')
WHERE NOT EXISTS ( SELECT * FROM range_views 
               WHERE range_id = '112'
               AND ip_address = '123.123.123.123'
               AND view_date = '2015-02-01');

the table has 4 columns

id - AUTO_INCREMENT
range_id - INT(6)
ip_address - VARCHAR(15)
view_date - VARCHAR(10)

Upvotes: 0

Views: 93

Answers (2)

axiac
axiac

Reputation: 72226

Create an UNIQUE INDEX on columns range_id, ip_address, view_date:

ALTER TABLE range_views
ADD UNIQUE INDEX id_ip_date(range_id, ip_address, view_date);

You need this index anyway; it will enforce your rule of adding new entries only if the value of (range_id, ip_address, view_date) pair does not already exist in the table.

Then INSERT the values and add IGNORE:

INSERT IGNORE INTO range_views(range_id, ip_address, view_date) 
VALUES('112','123.123.123.123','2015-02-01');

The documentation says:

If you use the IGNORE keyword, errors that occur while executing the INSERT statement are ignored. 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. With IGNORE, the row is discarded and no error occurs. Ignored errors generate warnings instead.

Upvotes: 1

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79929

This syntax is invalid. You have to rewrite it with the syntax INSERT INTO ... SELECT, something like this:

INSERT INTO range_views (range_id, ip_address, view_date) 
SELECT '112', '123.123.123.123', '2015-02-01'
FROM range_views
WHERE NOT EXISTS(SELECT * FROM range_views 
                 WHERE range_id = '112'
                   AND ip_address = '123.123.123.123'
                   AND view_date = '2015-02-01');

Upvotes: 4

Related Questions