insert and delete rows form a table

Question

I created a database called orthomcl

CREATE DATABASE orthomcl;
CREATE USER 'orthomcl'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON orthomcl.* TO 'orthomcl'@'localhost';
SELECT * FROM mysql.db WHERE Db = 'orthomcl'\G;

I then inserted a table called similarSequences to the database orthomcl

to check if I have duplicate entries in the table I used the following command

USE orthomcl;

select * from similarSequences group by query_id,subject_id having count(*)>1;

this command then returned the following result:

 134674 rows in set (5 min 20.81 sec)

Then I created a new table that will have only distinct rows.

create table holdup as select distinct * from similarSequences;

And this resulted in

 mysql> create table holdup as select distinct * from similarSequences;
 Query OK, 11320619 rows affected (5 min 53.82 sec)
 Records: 11320619  Duplicates: 0  Warnings: 0

Now, I would like to select distinct rows from the "holdup table", delete all the rows in similarSequence table and then insert the rows from the holdup table. I am not sure how to proceed further as this is my first time with mysql.

Answer 1

I think this is what you're trying to get at.

SELECT DISTINCT (rows) FROM holdup

DELETE (rows)

INSERT (rows) FROM holdup