delete from - a statement with a join

[kutchbhi]

The following query selects the rows that I want to delete-

select bad_rows.*
     from products as bad_rows
      inner join (
       select pid, MAX(last_updated_date) as maxdate
          from products
           group by pid
             having count(*) > 1
          ) as good_rows on good_rows.pid= bad_rows.pid
            and good_rows.maxdate <> bad_rows.last_updated_date;

Now to actually delete them I tried changing the first word - 'select' to 'delete', but in this case the query seems to run forever and no rows are deleted. Why ?

 

How else can these rows be deleted?

 

btw what I am trying to do is get rid of duplicates. the selelct query works just fine in selecting the records, all I have to do is figure out a way of deleteting them

Thanks

Edited May 14, 2014 by kutchbhi

[Barand]

 

Subqueries

Currently, you cannot delete from a table and select from the same table in a subquery.

 

Try create a temporary table using the subquery statement and use that in the delete statement instead

[Psycho]

Or, if this is a one-time operation, you could add a temporary column and use your query to update that column with a value for the records you want to keep (or the ones you want to delete). Then run a second query to delete the records based on the value in that column.

[Barand]

Unfortunately the same constraint applies to update statements too

 

 

 

Currently, you cannot update a table and select from the same table in a subquery.

[kutchbhi]

nvmind I added an index to the pid column and the delete worked fine!