Fairly simple query taking a long time to process

[Shal]

Hi all,

I have a table with 300,000 records in it and I want to run an update that has a few clauses in the WHERE command and just changes one boolean value on a subset of records. What I am curious about is the query, (if it works at all) takes ages to complete. I suppose 300,000 records is alot to process and I will sometimes want to change this boolean value on up to 20,000 of those, but I have had times of over an hour and it is still thinking. I usually break it and try again. I can break the query up into its component parts and they each run fine, but I think it is the combination of the "...Key NOT IN (..." part that causes the slowdown. The query is :

UPDATE Exclusion1 SET Exclusion1.Dumped = True
WHERE (Exclusion1.Job_Code = "xxxxxx" AND Exclusion1.Dumped = False)
AND Exclusion1.Key NOT IN (SELECT Key FROM Exclusion1 WHERE Exclusion1.Dumped =True);

NB: "Key" in this case is not the primary key just a number.

Is there a logic issue with my query? Should I be using EXISTS or something?
When I am using the query there is a subset within the table identified by a job code. Some of these records have the same key number as others. These records always have one that is marked as True and one as false. What I want to do is find all the records of the job code in question that are currently marked as false for the "Dumped" boolean and then set the flag to true for the ones that don't have multiple occurrences of the "Key" value.

Any ideas?
Thanks
Shal

[HiTechCoach]

By using a sub query ("NOT IN"), I would say that you do NOT had a simple query.

I thinks the speed issue comes form the fact the the sub query get re-run for every single row.

Is "Key" indexed? If not, try adding an index on the field.

[Shal]

Yeah the "Key" field has the indexed setting set to "Yes (Duplicates OK)"

So I guess I didn't understand the nature of the NOT IN command.

Sounds like I would need to export the required records to a temp table and run it on that then insert them back again after the query finishes.

Is there a more efficient way to do it than that?

Shal

[MMcCarthy]

As key is not a primary key this may not be very efficient either. Worth a try though. However, be sure to back up a copy of your table first.

UPDATE Exclusion1 LEFT JOIN Exclusion1 As E2
ON Exclusion1.Key = E2.Key
SET Exclusion1.Dumped = True
WHERE (Exclusion1.Job_Code = "xxxxxx" 
AND Exclusion1.Dumped = False) 
AND E2.Dumped =False);