Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2008
    Posts
    7

    Unanswered: Problem with where exists subquery

    Hi All,

    Im having a slight problem deleting/ updating some specific rows out of my table

    when i worked out the select statment i got the correct return on data however when i place the same logic into a delete or update it seems to apply the logic to everything any help would be much appreciated

    the select query:
    select distinct id, max(timeexecuted) from table1 group by id

    the delete query:
    delete table1
    where exists (select distinct id, max(timeexecuted) from table1 group by id)

    Regards Moz

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You need to corrolate your subquery. At present it deletes all rows from table1 where there is one or more rows in table1. Do you want to delete all rows where ids match and the timeexecuted matches the most recent timeexecuted?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by pootle flump
    You need to corrolate your subquery.
    or correlate

    either will do



    moz, regarding this:
    Code:
    select distinct id, max(timeexecuted) from table1 group by id
    i should like to point out that the purpose of GROUP BY is to aggregate

    since you have GROUP BY id, the query is guaranteed to produce one row per id

    furthermore, the MAX() of anything for a group is a single value

    so we have distinct ids, each with the corresponding max value

    therefore adding DISTINCT is totally superfluous in this query

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Feb 2008
    Posts
    7
    Yes i want to delete the most recent row (timeexcuted) with a unique id

  5. #5
    Join Date
    Feb 2008
    Posts
    7
    r937 sorry the id column is NOT unique.

    So we can have instances e.g.
    id time PK
    1 12:00 1
    2 13:00 2
    2 13:30 3
    3 14:00 4
    4 15:00 5
    3 16:00 6

    So all i want to do is delete row PK 2, PK 4

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    but this is exactly what i said!!!

    if you do a GROUP BY id, then you get unique ids, only one result row per id

    and there is only one MAX per id

    therefore DISTINCT is not required

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    here, i tested this, it works on your sample data:
    Code:
    DELETE dese
      FROM test_dupes AS dese
    INNER
      JOIN ( SELECT id
                  , MAX(time) AS maxtime 
               FROM test_dupes 
             GROUP BY id ) AS dose
        ON dose.id = dese.id
       AND dose.maxtime > dese.time
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Feb 2008
    Posts
    7
    r937 sorry didnt read what you had wrote correctly... but even with the distinct taken out i cant delete the correct data.

    ???

  9. #9
    Join Date
    Feb 2008
    Posts
    7
    r937 --- You legend :-)

    Just tested works!

    Thanks alot

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •