avoid duplicate rows mysql

Question

Every time when I run an insert on my table I want to avoid duplicates. If the unique link is already saved than the row should be updated. Would be possible to use in this case ON DUPLICATE KEY UPDATE? I have been trying out but it won't work

    id  cat_id  cat_name                 prod_group                     link                                                                                                                                                                                                                                                                                                            
------  ------  -----------------------  -----------------------------  ---------------------
     1       5  Notebooks                 Alienware                          /url_to/what_is/unique                                                                                                                                                                                               
    10       5  Notebooks                 Latitude                      /url_to/what_is/unique                                                                                          
    11       3  Desktops                  Alienware                     /url_to/what_is/unique                                                                                     
    12       3  Desktops                  Optiplex                      /url_to/what_is/unique rquery=na                                                                                                                                                       
    20       3  Desktops                  Legacy-System                 /url_to/what_is/unique rquery=na                                                                                   
    21       3  Desktops                  Studio                        /url_to/what_is/unique rquery=na                                                                                           
    22       1  Monitore und Elektronik   Axim Electronic               /url_to/what_is/unique %40%2CAxim%2BElectronic&rquery=na                                                           
    27       1  Monitore und Elektronik   TV                            /url_to/what_is/unique rquery=na                                                                
    28       2  Handys und Tablets        Tablet                        /url_to/what_is/unique rquery=na                                                                  
    29       2  Handys und Tablets        Mobile Device                 /url_to/what_is/unique rquery=na                                                 
    30       4  Drucker                   Printer                       /url_to/what_is/unique                                                         
    31       6  Server und Netzwerk       Cloud Product                 /url_to/what_is/unique

my query

$sql="INSERT INTO drivers_cat_copy(cat_id,cat_name,prod_group,link) 
                     VALUES('$id','$name','$p','$cat_url')".                     
                     "ON DUPLICATE KEY UPDATE cat_id='$id',cat_name='$name',prod_group='$p',link='$cat_url'";

table properties

CREATE TABLE `drivers_cat_copy` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `cat_id` int(1) DEFAULT NULL,
  `cat_name` varchar(64) DEFAULT NULL,
  `prod_group` varchar(64) DEFAULT NULL,
  `link` varchar(1054) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=77 DEFAULT CHARSET=latin1

Can you post the table schema and the query you say won't work? — Mike Purcell, Aug 02 '12 at 21:13
Please post what you've tried that isn't working, so we can help figure out why it's failing. — Ken White, Aug 02 '12 at 21:15

score 3 · Answer 1 · answered Aug 02 '12 at 21:15

3

Yes, you can use ON DUPLICATE KEY UPDATE if you want to update values for records with matching unique keys. Of course that means you have to have unique keys on the table to begin with.

answered Aug 02 '12 at 21:15

Mike Brant

70,514
10
99
103

score 1 · Answer 2 · edited May 23 '17 at 11:48

The 'link' column must be defined as a primary or unique key in order for the 'Insert .. on duplicate key update' syntax to work properly.

Execute a 'Describe TableName' statement to print out the description of the table and its keys.

Now that you've posted your table's schema, you can see that the table has only one key defined, a key for the id column.

You could do this:

alter table drivers_cat_copy add unique index link_uniq_index (link(1054));

however, it will not be very efficient. You should look at the first answer of this question for a better solution: MySQL: How to alter varchar(255) UNIQUE column to UNIQUE Text NOT NULL?

score 0 · Answer 3 · answered Aug 02 '12 at 21:15

0

You could write a stored procedure

proc_insert_or_update(var1, var2, varN, link)

if link exists UPDATE query ELSE INSERT query

http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html

answered Aug 02 '12 at 21:15

rk2z

58
3

1

Stored procedure as a solution for the OP is like killing a mosquito with a bazooka, overkill. – Mike Purcell Aug 02 '12 at 21:52

avoid duplicate rows mysql

3 Answers3