MySQL Ignore is not working

Question

I am trying to prevent duplicates from occuring using the following query, but it doesnt seem to work. Please could you tell me what the problem is?

INSERT IGNORE INTO Following SET `followingUserID` = '$accountIDToFollow', `followerUserID` = '$accountID'

@Michael there is no error produced, it just isn't ignoring duplicates. — max_, Jun 05 '11 at 18:52
http://stackoverflow.com/questions/548541/insert-ignore-vs-insert-on-duplicate-key-update — zod, Jun 05 '11 at 18:52

Ibu · Accepted Answer · 2011-06-05T19:18:45.960

1

INSERT IGNORE INTO 
    Following (`followingUserID`,`followerUserID`) 
VALUE 
   ('$accountIDToFollow','$accountID')

You were doing an UPDATE format before

If you are trying to do an update this is how it works

UPDATE followingUserID SET followingUserID = '$accountIDToFollow', WHERE followerUserID = '$accountID';

Of course you want to replace the were clause with the correct condition you want to do

edited Jun 05 '11 at 19:18

answered Jun 05 '11 at 18:52

Ibu

42,752
13
76
103

edit--- I am getting the error: 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 '' at line 1 – max_ Jun 05 '11 at 19:01
what are the values inside the 2 variables? – Ibu Jun 05 '11 at 19:03
the variables are 5 and 6. my code is: INSERT IGNORE INTO Following (`followingUserID`,`followerUserID`) VALUES ('$accountIDToFollow','$accountID' – max_ Jun 05 '11 at 19:04
This will not work look at my code above, you only need one entry in the first parentheses `(followingUserID)` – Ibu Jun 05 '11 at 19:06
ok, I pasted your query, and it entered the values in the same column on two different rows. Please see the image here:http://d.pr/jxY6 – max_ Jun 05 '11 at 19:15
Oh i have to apologizze i have noticed the second column was follower instead of following. ok i updated my query copy the code above now – Ibu Jun 05 '11 at 19:19
ok thanks, that works, but doesn't prevent duplication. I ran the script twice and it made two of the same rows instead of just one. – max_ Jun 05 '11 at 19:21
Maybe you should review your table schema. add unique index to those 2 columns – Ibu Jun 05 '11 at 19:25
ok clear your table, with truncate, the run this query `ALTER TABLE Following ADD UNIQUE (followingUserID,followerUserID) ;` – Ibu Jun 05 '11 at 19:29

score 1 · Answer 2 · answered Jun 05 '11 at 18:53

As per MYSQL documentation,

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. With IGNORE, the row still is not inserted, but no error is issued.

It means, the IGNORE does not prevent any record duplicate. You will have to put Unique constraints on your given fields.

so instead of using IGNORE what should I use to detect duplicates so that something is called if there is a duplicate? — max_, Jun 05 '11 at 19:49

MySQL Ignore is not working

2 Answers2