Update query not working or super slow? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Update query not working or super slow?

Author

Topic

worldofrugs
Starting Member

3 Posts

Posted - 2007-08-14 : 16:01:29

I am trying to make an update query but have some performance issues...

I need to compare a folder with pictures to an excel file, to see if the
pictures exist in the excell file when they exist in the folder...
I want to create 3 different things:
1 - The image exist in the folder but not in the excel file (add txt: Add
Image)
2 - The picture exist in both (add txt: Good)
3- Picture does not exist in the folder (add txt: No Picture)..

What I have done so far:
I have send the DIR command to a txt file and imported this into MS-Access,
removing the extentions, leaving only the names. Since all names are product
numbers it will result in the following: 123456.jpg --> 123456
I have called this table PDrive with the fields ID (autonumber) and Field1
(number)

Then I imported the Excell file that was exported from our software into a
table called RugMan, with the fields: ID (autonumber), Number (number),
Imagefile (number) and Test (text)

Now I need to compare Field1 with ImageFile.. They both are modified to only
have numers, so it should be easy right?

My queries:
1- Add Image
UPDATE PDrive, RugMan SET RugMan.Test = "Add Image"
WHERE ((([RugMan]![Number])=[PDrive]![Field1]) AND
(([RugMan]![ImageFile])<>[PDrive]![Field1]));

This one works great, no problems

2- Good
UPDATE PDrive, RugMan SET RugMan.Test = "GOOD"
WHERE ((([RugMan]![Number])=[PDrive]![Field1]) AND
(([RugMan]![ImageFile])=[PDrive]![Field1]));

Also works without any problems.

3-No Picture
UPDATE PDrive, RugMan SET RugMan.Test = "GOOD"
WHERE ((([RugMan]![Number])=[PDrive]![Field1]) AND
(([RugMan]![ImageFile])=[PDrive]![Field1]));

Here is the PROBLEM... This query I can let run for hours, but it never
seems to finish...

Anyone has any idea why and what the solution to this is?
Thanks!!

worldofrugs
Starting Member

3 Posts

Posted - 2007-08-14 : 16:39:42

MAde a mistake:
Quesry 3 should read:

UPDATE PDrive, RugMan SET RugMan.Test = 'No Pic'
WHERE ((([RugMan]![Number]) Not In (SELECT [PDrive]![Field1] FROM PDrive )));

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-08-14 : 17:00:51

try to limit the number of updates to a few then see how long it takes

Ashley Rhodes

worldofrugs
Starting Member

3 Posts

Posted - 2007-08-14 : 17:09:09

quote:
Originally posted by ashley.sql

try to limit the number of updates to a few then see how long it takes

Ashley Rhodes

Not sure what you mean by this Ashley... Do you mean to put in the statement something like: where recordX = "123456" ??

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources