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
– Mike Purcell, Commented Aug 2, 2012 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
– Ken White, Commented Aug 2, 2012 at 21:15

Mike Brant · Accepted Answer · 2012-08-02 21:15:27Z

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 2, 2012 at 21:15

Mike Brant

71.4k10 gold badges102 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 11:48:14Z

1

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?

edited May 23, 2017 at 11:48

CommunityBot

11 silver badge

answered Aug 2, 2012 at 21:16

Dan Carlson

9711 gold badge13 silver badges19 bronze badges

Comments

rk2z · Accepted Answer · 2012-08-02 21:15:16Z

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 2, 2012 at 21:15

rk2z

583 bronze badges

1 Comment

Mike Purcell Over a year ago

Stored procedure as a solution for the OP is like killing a mosquito with a bazooka, overkill.

Collectives™ on Stack Overflow

avoid duplicate rows mysql

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related