making a database in mysql without duplicate values

Question

I have a tab separated text file in the format

id | field 1 | field 2 ...

I want to insert this into a mysql database with id as the primary key but the text file may contain duplicate id's .

How to make sure that there's just one entry corresponding to each id.
How to make a choice between two lines having the same id (Yes, they might not be consistent, but it's okay to choose one over other like the first or the last occurrence )

KV Prajapati · Accepted Answer · 2011-12-24 08:43:05Z

2

Read line by line from text file, parse that line and use INSERT ... ON DUPLICATE KEY UPDATE Syntax.

answered Dec 24, 2011 at 8:43

KV Prajapati

94.8k20 gold badges151 silver badges188 bronze badges

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

Comments

Nobita · Accepted Answer · 2011-12-24 08:40:49Z

1

I would do a SELECT before INSERT and count the number of rows returned by the SELECT. Something like this:

SELECT * FROM yourTable WHERE yourTable.id = :id

If that returns any row, don't insert and go to next. Otherwise insert it.

Edit: This would be a post strategy. It would be good if you could add a Unique Constraint to guarantee uniqueness. Something like:

ALTER TABLE yourTable ADD CONSTRAINT ukID UNIQUE (id)

answered Dec 24, 2011 at 8:40

Nobita

23.8k11 gold badges63 silver badges87 bronze badges

2 Comments

R1234 Over a year ago

it's not in any table yet, it's in a text file. You mean I should first make a database and then another one out of it?

Nobita Over a year ago

Create a table that fits your needs (don't forget to add the UNIQUE CONSTRAINT for that id field). Then you can do either what I suggested or what @AVD suggested in order to add your file rows into the database. What AVD suggested is more efficient since you will need just one query to insert into the DB.

Cyberfox · Accepted Answer · 2011-12-24 10:14:48Z

0

Presuming a Unix shell, I'd do this:

awk '!x[$1]++' inputfile.tsv > uniqfile.tsv

then do your import off of the uniqfile.

edit: to be clear, that script uniq's the input file based on the first field by only outputting rows that do not already have a non-zero value in a hash keyed off of the first field.

edited Dec 24, 2011 at 10:14

answered Dec 24, 2011 at 8:48

Cyberfox

1,1558 silver badges13 bronze badges

Collectives™ on Stack Overflow

making a database in mysql without duplicate values

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related