How to check duplicate and upload the new data when I use PHP to upload the CSV file into MySQL database

Question

I have a CSV file which have ten column:

ID, fromDate, endDate, address, price1, price2, column1, column2, column3, column4

but I just want to insert the first six columns into the MySQL database, and most important, if there exist a tuple in the database that the attribute(ID, fromDate, endDate, address) are the same, We defined that the two rows are identify.

for example: there exist a tuple in the database:

(1000, 2014-11-21, 2014-11-23, new york, 100, 200)

and Now, in the CSV file, there exists a row:

(1000, 2014-11-21, 2014-11-23, new york, 150, 250)

We defined that the two tuple are the same. So now we should update the data in the database by the new data in the CSV file.

which means, delete the tuple

(1000, 2014-11-21, 2014-11-23, new york, 100, 200)

and insert the new tuple:

(1000, 2014-11-21, 2014-11-23, new york, 150, 250)

How to do this?

how to check for the duplicate when insert the data from CSV into database, and once found a duplicate row, how to replace it?

sectus · Accepted Answer · 2014-11-24 03:25:24Z

1

You could use specific mysql Replace

REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.

Or Insert ON duplicate, but with this approach you have to list all your fields.

answered Nov 24, 2014 at 3:25

sectus

15.7k5 gold badges59 silver badges97 bronze badges

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

2 Comments

Jie Over a year ago

So what you means is that I can defined the ID, fromDate toDate address to be primary key. and using the Replace insert statement to implement this?

sectus Over a year ago

@jyuan , keep id as primary key, but add unique index on id, fromDate, toDate, address.

Collectives™ on Stack Overflow

How to check duplicate and upload the new data when I use PHP to upload the CSV file into MySQL database

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related