Jump to content

delete from - a statement with a join


kutchbhi

Recommended Posts

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

Link to comment
https://forums.phpfreaks.com/topic/288493-delete-from-a-statement-with-a-join/
Share on other sites

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.

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.