Results 1 to 12 of 12
  1. #1
    Join Date
    Dec 2006
    Posts
    12

    Angry Unanswered: simple update query super slow?

    I am trying to a very simple update query but it takes for ever!
    the quesry compares two fields in 2 tables and if they do not match, it had to place the text "done" in a field of one of these tables...

    example:
    If aaa in table ZZZ <> bbb in table XXX, update ddd in table ZZZ to text "done"

    I have other queries that run fine that are simular:

    If aaa in table ZZZ = bbb in table XXX, update ddd in table ZZZ to text "todo"

    Anyone any ideas why this is happening? I waited for over 6 hours but still super slow!

    the real sql is:
    UPDATE PDrive, RugMan SET RMan.test = "done"
    WHERE ((([RugMan]![Number])<>[PDrive]![Field1]));


    MS-Access 2003
    XP

  2. #2
    Join Date
    Oct 2003
    Posts
    21
    Assuming you have at least one record in each table, isn't it always true that
    [RugMan]![Number] <> [PDrive]![Field1] ? At least most of the time.

    Where is the table join?

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by flipandboef
    UPDATE PDrive, RugMan SET RMan.test = "done"
    WHERE ((([RugMan]![Number])<>[PDrive]![Field1]));
    Missing bracket + you're updating two tables? Oooh and RugMan <> RMan...

    Gussery is correct, without a join, your query will lose itself
    George
    Home | Blog

  4. #4
    Join Date
    Dec 2006
    Posts
    12
    ok, perhaps my situation is not clear (my bad..)

    Let's try it again...
    I have two tables: Rugman and PDrive
    The table look like this:
    Rugman -- ID (Auto/key) / Number (text!) / location (text) / test (text)
    PDrive -- ID (Auto/key) / Field1 (text!)

    The reason I had to use text for Number and Field1, is because there are a few lines what have a number like: 1234-2

    I just want to check the Number in table Rugman and see if it does exist in the table PDrive... If it does: field test must be changed to "done" for that record where the numer exists in the other table...
    Don't get why it's running so slow.
    I have created also other update queries for the same tables and those complete good within seconds!
    Look for example at this one, that completes within 15 seconds:
    ---
    UPDATE PDrive, RugMan SET RugMan.test = "GOOD"
    WHERE ((([RugMan]![Number])=[PDrive]![Field1]) AND (([RugMan]![ImageFile])=[PDrive]![Field1]));
    ---

    If any of you can help me further (or someone else), please! It's not rocket science what I'm trying to do, is it??
    Thanks all!

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    EDIT: This is wrong, am re-writing it...
    EDIT: EDIT: Actually, I can't decide if this is right or not...
    Code:
    UPDATE	RugMan
    SET	RugMan.test = 'GOOD'
    WHERE	RugMan.Number IN
    	(
    	SELECT  PDrive.Field1
    	FROM	PDrive
    	)
    AND 	RugMan.ImageFile IN
    	(
    	SELECT  PDrive.Field1
    	FROM	PDrive
    	)
    That's my guess...
    George
    Home | Blog

  6. #6
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    If this is true:
    [RugMan]![Number]=[PDrive]![Field1] AND [RugMan]![ImageFile]=[PDrive]![Field1]

    then use this

    [RugMan]![Number]=[Rugman]![ImageFile]

    take care of the rest with a join

    UPDATE RugMan
    SET RugMan.test = "GOOD"
    FROM Pdrive INNER JOIN RUGMAN ON PDRIVE!FIELD1=RUGMAN![NUMBER]
    WHERE [RugMan]![Number]=[Rugman]![ImageFile]
    Inspiration Through Fermentation

  7. #7
    Join Date
    Dec 2006
    Posts
    12
    Thanks for those quick replies, but that one already worked fine and fast...
    When I want to compare Where Rugman.RugNumber <> PDrive.Field1 update RugMan.test to the text "done", it goes super slow / never finishes...
    I have tried the inner join, but no improvements...
    So to be sure what I need:
    Compare table Rugman, field Number with table Pdrive and if the field Number does not exist anywhere in the table Pdrive, then update field Test to "done"...

    I appiciate you all for helping, this is driving me nuts
    Last edited by flipandboef; 04-10-07 at 12:28.

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    This still feels wrong... subquery method, worth a shot...
    Code:
    UPDATE	RugMan
    SET	RMan.test = 'done'
    WHERE	[RugMan]![Number] NOT IN
    	(
    	SELECT [PDrive]![Field1]
    	FROM PDrive
    	)
    George
    Home | Blog

  9. #9
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Quote Originally Posted by flipandboef
    Compare table Rugman, field Number with table Pdrive and if the field Number does not exist anywhere in the table Pdrive, then update field Test to "done"...
    While this implies DOES exist.
    Quote Originally Posted by flipandboef
    WHERE ((([RugMan]![Number])=[PDrive]![Field1]) AND (([RugMan]![ImageFile])=[PDrive]![Field1]));
    Which is it?
    Inspiration Through Fermentation

  10. #10
    Join Date
    Dec 2006
    Posts
    12
    GeorgeV -->
    Thanks for thinking with me on this...
    I have tried your code, but I'm afriad it's still as slow as it was...
    What I don't get it that why the other queries go fast but this one crawls....

    RedNeckGeek -->
    It's the first one.. The second one is an example of another query that runs smooth and fast...
    So the one going slow (the problem discussed) is:
    Compare table Rugman, field Number with table Pdrive and if the field Number does not exist anywhere in the table Pdrive, then update field Test to "done"...

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    It crawls because your conditions are way off...

    Example:
    Code:
    tblA.Field1
    --
    1
    2
    3
    Code:
    tblB.Field1
    --
    1
    2
    3
    Code:
    SELECT tblA.Field1
    FROM tblA
    WHERE tblA.Field1 <> tblB.Field1
    Tell me what your results will be...
    George
    Home | Blog

  12. #12
    Join Date
    Dec 2006
    Posts
    12
    GeorgeV -->

    You tha man!!!
    It did not go as fast as the other queries, but it just got finished (took about 4 mins or so?).....
    Whatever it was, I need this process only once, so it's done!!!
    I'm very greatful.
    Thanks George!

Posting Permissions

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