Geek asks and answers

MySql Delete - same as select (join)

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 thought it would be simple:

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

but that will not work. Can someone tell me how to do this right?

After @bgdrl's answer, I think about starting only select, get the identifier of all the records that need to be copied, and then deleted; but I think there should be a simpler solution, anyone?

Even if the marked @bgdrl answers as the correct answer, it’s only because of this fact.

Who cares what I did: I made the same choice that I started (but chose only the id column, since selecting all the columns would kill my bad computer), exported it to INSERT STATMENTS(using mysqlworkbench), opened a text file in notepad, replaced everything INSERT INTO... with DELETE FROM WHERE ID=, and run this query in mysql.

I'm so stupid using this path, but apparently had no other choice.

+3
source share
2 answers

PLEASE CONTACT THE TABLE BEFORE THE NEXT STEPS.

Follow next 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 can execute it with a single transaction using TEMPORARY TABLES .

Happy request!

0
source

mysql: " . , DELETE, INSERT, REPLACE, UPDATE". http://dev.mysql.com/doc/refman/5.1/en/subqueries.html

+2

More articles:


All Articles