mysql query to update duplicate entries

Question

i am stuck in a simple update query. i have a table say tabble1 containing 'name' and 'phone_no' column. Now when i upload csv file containing list of name and contact numbers, i want to update name of duplicate number with previous one. For ex. i have a row containing 'max' '8569589652'. now when i upload same number with another name say 'stela' '8569589652' then stela shuld get updated to max.

for this purpose i created another table say table2. then i collected all duplicate entries from table1 into table2. after that updated new entry with previous name.

following are my queries: to collect all duplicate entries:

INSERT INTO table2 SELECT phone_no,name FROM table1 
            GROUP BY phone_no HAVING COUNT(*)>1;

to update duplicate entries in table1:

UPDATE table1.table2 SET table1.name=table2.name 
       WHERE table1.phone_no=table2.phone_no ;

My problem is when i run these two query it is taking tooo much of time. It is taking ore than half an hour to upload csv file of 1000 numbers. Please suggest me optimize query to upload csv in less time.

does speed of uploading matters with size of database.. please help.

thanks in advance.

cartina · Accepted Answer · 2013-08-02T06:13:35.803

1

Here are the steps from the link I suggested.

1) Create a new temporary table.

CREATE TEMPORARY TABLE temporary_table LIKE target_table;

2) Optionally, drop all indices from the temporary table to speed things up.

SHOW INDEX FROM temporary_table;
DROP INDEX `PRIMARY` ON temporary_table;
DROP INDEX `some_other_index` ON temporary_table;

3) Load the CSV into the temporary table

LOAD DATA INFILE 'your_file.csv'
INTO TABLE temporary_table
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(field1, field2);

4) Copy the data using ON DUPLICATE KEY UPDATE

SHOW COLUMNS FROM target_table;
INSERT INTO target_table
SELECT * FROM temporary_table
ON DUPLICATE KEY UPDATE field1 = VALUES(field1), field2 = VALUES(field2);

5) Remove the temporary table

DROP TEMPORARY TABLE temporary_table;

edited Aug 02 '13 at 06:13

answered Aug 02 '13 at 05:42

cartina

1,409
13
21

i am using following query to upload csv file: LOAD DATA INFILE '$file_name' INTO TABLE import FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n' IGNORE 1 LINES (@srno,@customer_name,@mobno) SET customer_name=@customer_name,mobno=@mobno,datetime='$datetime' please help me to write ON DUPLICATE KEY UPDATE clause with this – Roshan G Aug 02 '13 at 06:04
which query? post it here. – cartina Aug 02 '13 at 06:06
ok. Pls check this link and follow the steps. http://stackoverflow.com/questions/15271202/mysql-load-data-infile-with-on-duplicate-key-update – cartina Aug 02 '13 at 06:10
@RoshanG Please check my edited answer and the link. You will be able to resolve the problem. – cartina Aug 02 '13 at 06:15
can u please suggest what values should i replace with field1 and field2.?? what i want is , i want to replace new name with old name if duplicate phone_no entry occures.... please help. i am a bit confuse... – Roshan G Aug 02 '13 at 06:53
Try this and let me know if this is a problem. INSERT INTO target_table SELECT * FROM temporary_table ON DUPLICATE KEY UPDATE phone = VALUES(phone); – cartina Aug 02 '13 at 06:58
1. CREATE TEMPORARY TABLE temporary_table LIKE target_table; 2. DROP INDEX `PRIMARY` ON temporary_table; 3. LOAD DATA INFILE 'your_file.csv' INTO TABLE temporary_table FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' (field1, field2); (This is already working in your case) 4. INSERT INTO target_table SELECT * FROM temporary_table ON DUPLICATE KEY UPDATE phone = VALUES(phone); This is all you need to get it work. – cartina Aug 02 '13 at 06:59
its working.. but there is an issue. i want name to be updated. but now its updating mobile number. – Roshan G Aug 02 '13 at 07:22
@RoshanG ok. Change step 4. INSERT INTO target_table SELECT * FROM temporary_table ON DUPLICATE KEY UPDATE name = VALUES(name); This will do. – cartina Aug 02 '13 at 07:23
dear cartina is it possible to update selected fields of row. i have other columns in row but i only want to update name and datetime of that duplicate contact number – Roshan G Aug 02 '13 at 07:51
INSERT INTO target_table SELECT * FROM temporary_table ON DUPLICATE KEY UPDATE name = VALUES(name), datetime = VALUES(datetime); In the last line " ON DUPLICATE KEY UPDATE field1 = VALUES(field1), field2 = VALUES(field2),field3 = VALUES(field3)", you can add any many columns as you need separated by comma which you need to update. – cartina Aug 02 '13 at 07:56
dear cartina thanks for being. but it still not working as i wanted. i will tell u my pain now... i daily get csv file of thousands of contacts numbers along with name, city, state, datetime. now when i insert new csv file, if the contact number exists in database i want to update name to older one and datetime to current datetime. but it is not working.... – Roshan G Aug 02 '13 at 08:16
can u please illaborate ON DUPLICATE KEY clause... on which duplication it updates values... is it necessory to add unique or any other key in target table – Roshan G Aug 02 '13 at 08:17
In your case 'phone' should be the primary/unique key because you want to update on the basis of phone. It updates on duplication of primary/unique key. So phone number must be unique. What is your primary/unique key? – cartina Aug 02 '13 at 08:53
@RoshanG http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html Check this link to understand the concept. Make phone as unique key in your target table and let me know the result. – cartina Aug 02 '13 at 08:55
i want old value of name to be retain if duplicates of number occure. but here old name is getting replaced by new.. please help – Roshan G Aug 02 '13 at 10:15
thanks alot cartina... done with the bug.. thanks again for patiently being on the portal and tolarating my dumbness... thank u so much.. – Roshan G Aug 02 '13 at 10:22

score 0 · Answer 2 · edited Aug 02 '13 at 05:55

0

Dump your CSV file in a temp table.

Then aply merge statement simply

Merge AS MAIN USING AS TEMP

On MAIN.CONTACT_NO=TEMP.CONTACT_NO WHEN MATCHED THEN UPDATE MAIN.NAME=TEMP.NAME; IF YOU WANT TO INSERT NON MATCHING RECORD USE THIS WHEN NOT MATCHED THEN INSERT

(NAME, CONTACT_NO) VALUES ( TEMP.NAME, TEMP.CONTACT_NO );

Please not that merge command must end with ';' I have used ';' after upadate remove that and add the below part and end the whole merge with ';'

Hope this helps

Please update if any more help needed.

edited Aug 02 '13 at 05:55

peterm

91,357
15
148
157

answered Aug 02 '13 at 05:52

Ashutosh Arya

1,138
2
8
14

please take merge systax from http://technet.microsoft.com/en-us/library/bb510625.aspx some how systax got lost when posted – Ashutosh Arya Aug 02 '13 at 05:54
OP is using MySql not Sql Server – peterm Aug 02 '13 at 05:55

score 0 · Answer 3 · answered Aug 02 '13 at 05:53

0

You can update duplicate entries of "phone no" like below.

INSERT INTO table2 (phone_no,name)
      VALUES
      ('11111', aaa),
      ('22222', bbb),
      ('33333', cccc),
ON DUPLICATE KEY UPDATE
     phone_no = VALUES(phone_no);

answered Aug 02 '13 at 05:53

Balaji Kandasamy

4,446
10
40
58

will i have to add unique key to phone_no column..?? or will it work without.?? – Roshan G Aug 02 '13 at 06:00
i am using following query to upload csv file: LOAD DATA INFILE '$file_name' INTO TABLE import FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n' IGNORE 1 LINES (@srno,@customer_name,@mobno) SET customer_name=@customer_name,mobno=@mobno,datetime='$datetime' please help me to write ON DUPLICATE KEY UPDATE clause with this – Roshan G Aug 02 '13 at 06:07

mysql query to update duplicate entries

3 Answers3