MySql Delete - same like select ( join )

Question

I copied some records from one table to another with this query :

insert into pages_finished (keyword,pages,resultlist,done,current)
select keyword,pages,resultlist,done,current 
 from pages_done o  
 where  (select count(*) as totalPages from pages_done x  where x.keyword = o.keyword)-1 = pages

Now I want to delete the same records from the source table, I was thinking it would be simple as:

delete from pages_done o  
 where  (select count(*) as totalPages from pages_done x  where x.keyword = o.keyword)-1 = pages

but that doesn't work. Could anyone tell me what is the right way to do that?

After @bgdrl answer, I'm thinking about running only the select, get the id's of all records that should be copied, and then delete; but I think there must be an easier solution, anyone?

Even though marked @bgdrl answer as the right answer, it is only because of that a fact.

To anyone interested with what I ended up doing : I did the same select I started with (but selected only the id column, since selecting all the columns would have killed my poor computer), exported it to an INSERT STATMENTS (using mysqlworkbench), opened the text file in notepad, replaced all the INSERT INTO... with DELETE FROM WHERE ID=, and run that query in mysql.

I feel so stupid using this way, but had no other choice apparently.

Igor · Accepted Answer

PLEASE BACKUP THE TABLE BEFORE FOLLOWING THE STEPS.

Follow the following STEPS

STEP 1

CREATE TABLE pages_done_ids 
    SELECT o.id FROM pages_done AS o  
    WHERE 
    (
        SELECT count(*) AS totalPages 
        FROM pages_done AS x  
        WHERE x.keyword = o.keyword
    )-1 = o.pages

STEP 2

DELETE FROM pages_done AS o  
WHERE o.id IN (SELECT id FROM pages_done_ids)

STEP 3

DROP TABLE pages_done_ids;

OK, you may accomplish it with one transaction using TEMPORARY TABLES.

Happy Querying!

MySql Delete - same like select ( join )

Answers (2)

Related Questions