Hu Qinan wrote: >Dear all, > >I intend to write a large volume of records to a table tbl. > >tbl: >fld1, int unsigned not null auto_increment primary key, >fld2, text >fld3, text > >The combination of (fld2, fld3) should be be unique, so I need to check for >duplicates every time when a record is added. >-->Question1: How to speed up insertions to this table in MySQL? > >To speed up the insertion, I try to write all records (with duplicates of fld_2 and >fld_3) into a temporary table. > CREATE TEMPORARY TABLE T ( > ID INT UNSIGNED NOT NULL AUTO_INCREMENT, > fld2 TEXT > fld3 TEXT); > >Then I try to select distinct fld2 and fld3 from the temproary table. And then insert >them into tbl. > INSERT INTO tbl SELECT DISTINCT fld1, fld2 FROM T; >But it does not work, since the column counts do not match. > >I try to use > INSERT INTO tbl SELECT DISTINCT * FROM T; >But since all IDs are distinct, all records in T will be inserted into tbl. >-->Question2: How to insert records with distinct (fld2 and fld3) into tbl in MySQL? > > If the number / order of columns differs, you have to specify them. It should be:
insert into tbl (fld2, fld3) select distinct fld2, fld3 from T; -- Daniel Kasak IT Developer * NUS Consulting Group* Level 18, 168 Walker Street North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: www.nusconsulting.com --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
