mysql - copy from t1 into new table t2 filtering out duplicate rows -

i have table t1, rows have duplicates in columns except id.
t1's id auto_increment , has 1mil rows.

t2 new table without data , id not need auto_increment create new column this.

q: after create t2, how can copy t1 t2 distinct values t1 in columns, t2 has no duplicate rows

i on amazons rds engine=innodb

t1 - have

+---+-----+-----+------+-------+ |id |fname|lname|mytext|morevar| |---|-----|-----|------|-------| | 1 | joe | min | abc  | 123   | | 2 | joe | min | abc  | 123   | | 3 | mar | kam | def  | 789   | | 4 | kel | smi | ghi  | 456   | +------------------------------+  

t2 - end with

+---+-----+-----+------+-------+ |id |fname|lname|mytext|morevar| |---|-----|-----|------|-------| | 1 | joe | min | abc  | 123   | | 3 | mar | kam | def  | 789   | | 4 | kel | smi | ghi  | 456   | +------------------------------+   

this attempt, got: error code: 1136. column count doesn't match value count @ row 1

insert t2 (id,fname,lname,mytext,morevar) select distinct st.mytext t1 st  st.id>0   , st.id<=1000  

the easiest way use group by:

insert t2 (id,fname,lname,mytext,morevar)     select id,fname,lname,mytext,morevar     t1 st     st.id>0 , st.id<=1000      group mytext; 

however, technically, not correct because column values not guaranteed come same row. so, right way is:

insert t2 (id,fname,lname,mytext,morevar)     select st.id, st.fname, st.lname, st.mytext, st.morevar     t1 st join          (select mytext, min(id) minid           t1           group mytext          ) mint          on st.id = minid     st.id>0 , st.id<=1000 ;