mysql查询更新重复的条目

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.

我陷入了一个简单的更新查询。我有一个表格说tabble1包含'name'和'phone_no'列。现在当我上传包含姓名和联系号码列表的csv文件时,我想更新前一个重复号码的名称。对于前者我有一行包含'max''8569589652'。现在,当我上传相同的号码与另一个名字说'stela''8569589652'然后stela shuld更新到最大。

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.

为此我创建了另一个表table2。然后我从table1收集所有重复的条目到table2。之后使用以前的名称更新了新条目。

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:

更新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.

我的问题是,当我运行这两个查询时,它花费了太多时间。上传1000个号码的csv文件需要花费半个多小时。请建议我优化查询以在更短的时间内上传csv。

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

上传数据库大小的速度..请帮忙。

thanks in advance.

提前致谢。

3 个解决方案

#1

Here are the steps from the link I suggested.

以下是我建议的链接的步骤。

1) Create a new temporary table.

1)创建一个新的临时表。

CREATE TEMPORARY TABLE temporary_table LIKE target_table;

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

2)(可选)从临时表中删除所有索引以加快速度。

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

3)将CSV加载到临时表中

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

4)使用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

5)删除临时表

DROP TEMPORARY TABLE temporary_table;

#2

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);

#3

Dump your CSV file in a temp table.

将CSV文件转储到临时表中。

Then aply merge statement simply

然后简单地合并声明

Merge AS MAIN USING AS TEMP

合并为主要用作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

在MAIN.CONTACT_NO = TEMP.CONTACT_NO匹配,然后更新MAIN.NAME = TEMP.NAME;如果您想要插入非匹配记录,则在插入时不匹配

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

(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 ';'

请注意,merge命令必须以';'结尾我用过 ';'在upadate删除它并添加下面的部分并以';'结束整个合并

Hope this helps

希望这可以帮助

Please update if any more help needed.

如果需要更多帮助,请更新。

#1