Error in my syntax when checking if exists before inserting

Question

I am trying to make sure a row doesn't exist before inserting but am getting a syntax error, here's my sql:

IF NOT EXISTS 
                (   SELECT  *
                    FROM    profile_views
                    WHERE   (profile_view_user = ?
                    AND     profile_view_viewing = ?)
                )
                BEGIN
                INSERT INTO profile_views
                (
                    profile_view_user,
                    profile_view_viewing,
                    profile_view_ip
                )
                VALUES
                (
                    ?,
                    ?,
                    ?
                )
                END;

CREATE TABLE IF NOT EXISTS `profile_views` (
  `profile_view_id` int(11) NOT NULL,
  `profile_view_user` int(11) NOT NULL,
  `profile_view_viewing` int(11) NOT NULL,
  `profile_view_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `profile_view_ip` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

My error I am getting is:

Warning: PDOStatement::execute(): SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF NOT EXISTS ( SELECT * FROM profile_views WHERE (pro' at line 1 i

How do I fix my sql query?

In MySQL you need a shell around this statement (like a procedure or function). Only queries can stand for themselfs — juergen d, Nov 12 '15 at 16:33
use insert ignore instead? http://stackoverflow.com/questions/1361340/how-to-insert-if-not-exists-in-mysql — xQbert, Nov 12 '15 at 16:34
shouldn't you be checking for the existence of the table ***before*** attempting to insert records into it? e.g. if table does not exist, create it, then if records don't exist, create them. Rather than if records don't exist create them in a table that may not yet exist? (Wouldn't the first statement completely bomb out either way if the table didn't exist and you're trying to select from it?) Also you don't need to `select *` for checking for existence, a single column should suffice. — Kritner, Nov 12 '15 at 16:36
If you define a primary key for the table you can use INSERT IGNORE to ignore the duplicate key error, or use the ON DUPLICATE KEY UPDATE to catch the duplicate key error (these are MySQL-specific) — DBug, Nov 12 '15 at 16:37
@Kritner I think the table schema is not part of the query being run (I might be wrong). — Vatev, Nov 12 '15 at 16:37
@DBug but that would only allow 1 record matching 1 row/collum — , Nov 12 '15 at 16:42
@randommman you can define `UNIQUE`/`PRIMARY` constraints on multiple columns — Vatev, Nov 12 '15 at 16:45
it would not allow "duplicate" rows, where duplicate is defined as a unique combination of primary key columns. The primary key can be more than one column. Since you're checking if a row exists with the specified values for 3 columns, that implies that the set of values for these 3 columns has to be unique, which means that it is the primary key (or at least a unique index). — DBug, Nov 12 '15 at 16:50
But the user can view other profiles, and because they're unique ids it won't insert, I'm super confused, can you create an answer instead please? — , Nov 12 '15 at 17:18

Iffo · Accepted Answer · 2015-11-12T17:37:41.117

0

Try using:

INSERT INTO profile_views(profile_view_user, profile_view_viewing, profile_view_ip)
SELECT * FROM (SELECT ?, ?, ?) AS tmp
WHERE NOT EXISTS (
    SELECT  * FROM profile_views WHERE
         (profile_view_user = ? AND profile_view_viewing = ?') LIMIT 1;

edited Nov 12 '15 at 17:37

answered Nov 12 '15 at 17:24

Iffo

343
5
18

Error in my syntax when checking if exists before inserting

1 Answers1