How to prevent duplicate info when we do mysql insert?

Welcome to Netspec

Feel free to contact us if you need any help!

E-commerce | Web server | Web design | Software design | Technical support

Have a look our work samples, see what we can do for you.

View frequent asked questions first if you are a new user. For more info, you can check forum or search our knowledge database.

Monthly Payment | One off payment

You are here: Forum Home >>

Post new topic

Master:

# 1 How to prevent duplicate info when we do mysql insert?

hans


Online	no
State
Grouping	Member
Class	class4
Score	230
Wealth	160
Posts	161
Login	21:29:50

i have experience with batch products upload for an online shop web site. All info are stored in mysql query file called products.sql before upload, so prevent duplicate is important. I simple use the following syntax:

insert into table(title,qty) values("USB flash abb",'100') on duplicate key update qty='100';

The title column is unique. If it does not exist, perform insert ,otherwise it exists, perform update.

Other ways:
REPLACE INTO table set title='title',qty='100'
INSERT IGNORE INTO table set title='title',qty='100'
The second one: if there are errors in products.sql, the query will be stopped

Hits: 813, Date: 2010-03-30 00:46:24

[Agree] ( 16

) [Against] ( 14

)

Quote |

Edit

# 2 the reason i don't use "REPLACE INTO"

hans


Online	no
State
Grouping	Member
Class	class4
Score	230
Wealth	160
Posts	161
Login	21:29:50

we certainly have other columns, e.g., productID, price, etc, if i use:
REPLACE INTO table set title='title',qty='100'
if the title already exists, the productID will be changed to new one since it is primary auto increment key, and the price will be changed to 0. This is not what we want.

Hits: 812, Date: 2010-03-30 01:04:59

[Agree] ( 13

) [Against] ( 18

)

Quote |

Edit

# 3 the reason that i don't use "INSERT IGNORE INTO "

hans


Online	no
State
Grouping	Member
Class	class4
Score	230
Wealth	160
Posts	161
Login	21:29:50

this one does not update, but we need to update qty or some info.
This one will not stop if there are some query errors, apparently, it is safe and reliable.

Hits: 811, Date: 2010-03-30 01:06:59

[Agree] ( 15

) [Against] ( 17

)

Quote |

Edit

# 4 I guess the only drawback of your selection is the size of products.sql

Dude


Online	no
State
Grouping	Member
Class	class4
Score	161
Wealth	125.5
Posts	116
Login	00:20:49

your query is long, contains both insert and update query. any problem if you need to upload a large file to the server?

Hits: 809, Date: 2010-03-30 01:09:37

[Agree] ( 17

) [Against] ( 18

)

Quote |

Edit

# 5 if you don't want to update, "INSERT IGNORE INTO" could be a choice

exp


Online	no
State
Grouping	Member
Class	class4
Score	360
Wealth	225
Posts	278
Login	17:57:23

INSERT IGNORE INTO table set title='title',qty='100'

if i am sure there will be no error in the file.

Hits: 806, Date: 2010-03-30 07:16:24

[Agree] ( 16

) [Against] ( 14

)

Quote |

Edit

1 - 5 [ 5]



						nothing