Jump to content


Photo

delete from - a statement with a join


  • Please log in to reply
4 replies to this topic

#1 kutchbhi

kutchbhi

    Member

  • Members
  • PipPip
  • 19 posts

Posted 14 May 2014 - 01:20 PM

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 by kutchbhi, 14 May 2014 - 01:24 PM.

http://workfreely.me - Search multiple freelancing sites in one go!

 

http://fallinprices.com - Flipkart price drops notifications and graphs!


#2 Barand

Barand

    Sen . ( ile || sei )

  • Gurus
  • 14,361 posts
  • LocationCheshire, UK

Posted 14 May 2014 - 02:24 PM


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


moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts
|baaSelect| generate js and php code for dynamic linked dropdowns

 


#3 Psycho

Psycho

    Advanced Member

  • Gurus
  • 10,806 posts
  • LocationCanada

Posted 14 May 2014 - 02:27 PM

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.


The quality of the responses received is directly proportional to the quality of the question asked.

I do not always test the code I provide, so there may be some syntax errors. In 99% of all cases I found the solution to your problem here: http://www.php.net

#4 Barand

Barand

    Sen . ( ile || sei )

  • Gurus
  • 14,361 posts
  • LocationCheshire, UK

Posted 14 May 2014 - 02:36 PM

Unfortunately the same constraint applies to update statements too

 

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

moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts
|baaSelect| generate js and php code for dynamic linked dropdowns

 


#5 kutchbhi

kutchbhi

    Member

  • Members
  • PipPip
  • 19 posts

Posted 14 May 2014 - 03:08 PM

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


http://workfreely.me - Search multiple freelancing sites in one go!

 

http://fallinprices.com - Flipkart price drops notifications and graphs!





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

Cheap Linux VPS from $5
SSD Storage, 30 day Guarantee
1 TB of BW, 100% Network Uptime

AlphaBit.com