Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2004
    Posts
    100

    Unanswered: Delete query help!

    Hello all,

    Here is a simplified version of what I'm trying to do:


    Table:

    Name……….Date……..Created……....Modified……..Job
    John………..5/5/2003……5/10/2003………………….300
    Pete………..5/8/2003……5/15/2003………………….315……………delete me!
    Pete………..5/8/2003……5/15/2003……5/17/2003…315
    Sam………..7/7/2003……7/10/2003……7/15/2003….350……………delete me!
    Sam………..7/7/2003……7/10/2003……7/18/2003….350

    I am hoping to come up with a query that will…..

    Figure out the records in which all fields match except the MODIFIED field. Where this happens I want to do the following:

    1) Delete one of the records that matches but has a blank MODIFIED field
    2) Delete one of the records that matches but has a MODIFIED field that is greater than a previous MODIFIED field (Record was modified and then modified again)


    Thanks!

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Why not go a different route and select records with a max modified date into a new table?

    SELECT * INTO yourNewTable
    FROM yourTable
    WHERE Modified = (SELECT MAX(Modified) FROM yourTable t1 WHERE t1.Name = yourTable.name)
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Jan 2004
    Posts
    100
    I've been able to do that but I need to delete them as well.

    Thanks, Norm

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    What's the difference?

    You end up with a dataset that does not have the records you want deleted... correct?

    If you just HAVE to have this in a delete query, change the above into a select statement and store it as a view (query, whatever). Then use a DELETE statement similar to this:

    DELETE yourTable
    WHERE yourTable.id NOT IN (SELECT id FROM yourView)
    Last edited by Teddy; 07-27-04 at 11:54.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Jan 2004
    Posts
    100
    There are about 110,000 stored on a SQL Server table and there are very few duplicates. If I pull all records that I don't want deleted, it will take a very long time to update these records that are already stored on the table.

    Thanks for the help.

    Norm

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Uh, no it wouldn't. 110,000 is NOTHING to MSSQL.

    There's no way to do this without doing some contextual comparisons.

    good luck with that, let me know how you end up getting it done.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Ok so this one was bugging me.


    I decided to run some performance tests just for shits.

    I created a table using this script:

    Code:
    CREATE TABLE #temp (f1 INT, f2 INT)
    GO
    
    DECLARE @x INT
    DECLARE @rx INT
    SET @x = 1
    WHILE @x < 110000
    BEGIN
    	SET @rx = ROUND(RAND() * 100, 0)
    	INSERT INTO #temp(f1, f2)
    	VALUES (@x, @rx)
    	SET @x = @x + 1
    END
    So now I have a 110,000 record table with one field ranging 1-110,000 and another field of random numbers ranging from 0 to 100.

    This query takes about 28 seconds (give or take a second) to execute.

    Then I ran this query, which is identicle to the one you said you have a performance issue with:

    Code:
    SELECT * FROM #temp WHERE f1 = (SELECT MAX(f1) FROM #temp t1 WHERE t1.f2=#temp.f2)
    GO
    DROP TABLE #temp
    As expected, this query returned 101 records with the highest f1 value for a given f2 random value.

    This query took 1 second to run, including the drop. Now granted, I probably recieved a nominal performance gain using a temp table, regardless, I don't think 1-2 seconds is so bad. If 2 seconds is STILL not good enough, you could compile the whole thing into an sp and regain a little time.
    Last edited by Teddy; 07-27-04 at 13:14.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  8. #8
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    Calm down man maybe he's working with a 486 and dial-up
    Ryan
    My Blog

  9. #9
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by rguy84
    Calm down man maybe he's working with a 486 and dial-up
    That wouldnt' matter. All the processing is done server side.



    And yes, I'm a performance nazi.

    *displays 'performance nazi' badge*
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  10. #10
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    hahaha W00t
    Ryan
    My Blog

Posting Permissions

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