quick question about update queries

[martinmike2]

Hello,

I am trying to run an update query that updates 70 fields in a table
from another table with matching field names. When I run this query,
it either takes about an hour to run or my PC just hangs. Am i
running up against some limit in access?

I am thinking about spliting the update into several smaller queries.

 

[boblarson]

Sounds like you may have normalization issues. Why do you have 70 fields and
why are you trying to update them all? It also sounds like you are basing
the updates on some text value instead of a numeric primary key which can be
indexed and provide better speed.

--
Bob Larson

Free Tutorials and Samples at http://www.btabdevelopment.com

__________________________________

 

[martinmike2]

I have normalization issues because I am recieveing my updated
information from a de-normalized database, so I have to deal with a
poorly designed system.

We keep a table filled with employee information. Every month we get
an update from higher-up that has updated information in it. What I
am trying to do is run an update query to update the records in the
"old table" with the values of the records in the "new table". The
primary key is the SSN field ( the Social Security Number). This
field is indexed.

 

[martinmike2]

I figuered out what I did wrong. I forgot to join the tables.

 

[Roger Carlson]

Have you tried doing it in two steps? First delete the records from the old
table that exist in the new table and then append all the records from the
import table. This assumes, of course, that all the information for the old
table is in the import table. It would go something like this:

DELETE * FROM OldTable WHERE SSN IN (SELECT SSN FROM ImportTable);

then

INSERT INTO OldTable SELECT ImportTable.* FROM ImportTable;


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[John Spencer]

How many records in the table?

You can have a max of 32 indexes so you cannot index all the fields to speed
up comparisons, but you could make indexes on the more likely fields.

Would you care to show the SQL you are using?

You MIGHT be better off handling this situation in a series of queries.

UPDATE Employees INNER JOIN NewTable
ON Employees.SSN = NewTable.SSN
SET Employees.LastName = [NewTable].[LastName]
WHERE Employees.LastName <> [NewTable].[LastName]
OR Employees.LastName is Null

With other fields you might need something like

UPDATE Employees INNER JOIN NewTable
ON Employees.SSN = NewTable.SSN
SET Employees.ApartmentNo = [NewTable].[ApartmentNo]
WHERE Employees.ApartmentNo & "" <> [NewTable].[ApartmentNo] & ""

If you have an index on ApartmentNo then the where clause might be more
efficient with this version of the where clause.

WHERE Employees.ApartmentNo <> [NewTable].[ApartmentNo]
OR (Employees.ApartmentNo is Null And NewTable.ApartmentNo is Not Null)
OR (Employees.ApartmentNo is Not Null and NewTable.ApartmentNo is Null)

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[John W. Vinson]

I figuered out what I did wrong. I forgot to join the tables.

<SNORK!!!>

Yep. That'd do it bigtime... not to mention trashing all the data in your
table. Hope you had a backup!