Mass update of fields in table that are empty strings...

[cariengon]

I may be dreaming... But is there a way to run one query, or one simple piece of VB code that updates each field in each record if it has an empty string for the whole table? I want to update them all to null so I know that the queries that I have created that look for null are working correctly. I have a table with over 100 fields and doing this manually for each field is quite time consuming... I discovered (thankfully using a copy) that if you try to do this in a query for more than one field, it udpates all the fields to null, even if there is something in it already...

My issue is that there is an import from an Excel file that is passing on empty strings and not Null. I have 2 things I need to do in order to stop this from happening 1) fix the excel file to return ISBLANK if there is nothing in a cell that the import worksheet references and 2) fix the append/update queries to pass Null instead of the empty string during the import process.

If I can find a way to update the tables at one time periodically, I'd much rather do this than to go back through the several different queries and Excel file - which would mean 500+ fixes vs. what I hope to be one easy fix...

Any ideas are greatly appreciated.
Carie

 

[ByteMyzer]

You might try something along the lines of:
[tt]
UPDATE Table1
SET Table1.Field1 = Null
WHERE Nz(Table1.Field1,'')='';
[/tt]
The above example will update Field1 in Table1 to Null only if the field is empty.

See if this works for you.

 

[JonFer]

You could use:

Set Field1=IIf(Field1="",Null,Field1),
Field2=IIf(Field2="",Null,Field2),
...

Don't use any where conditions and it will update every field in the table even if it isn't the empty string but the new value will only be different if the current value IS the empty string.

You could also program a function to loop through the Fields collection of your table and update each field separately with a query like ByteMyzer's.


John

http://www.interautomateconsulting.com

 

[ByteMyzer]

JonFer, your solution works for a small amount of data, but for large amounts (in the thousands of records) my solution is much quicker because it doesn't waste time on the records where the field in question does have data.

 

[JonFer]

If there are many unindexed fields, I am not sure that your solution would be faster when updating 100 fields. Reading through 1000's of large records 100 times instead of once, hmmm...

John

http://www.interautomateconsulting.com

 

[ByteMyzer]

Actually, Jonfer, the criteria selection method is faster than the Iif method.

I tested both methods on a table in a database stored on a network drive over 100MB throughput. The table had 10,000 records in it, 3,000 of which were initially set to "" in a non-indexed field. I ran the test ten times, and the criteria selection method took only 47-52% of the time that the Iif method did.

I then switched the proportions: 10,000 records with 7,000 of them set as "" in the non-indexed field. I again ran the test ten times, and the criteria selection method took only 77-85% of the time that the Iif method did.

Conclusion, Using a criteria in the query is faster, even on a non-indexed field.

 

[traingamer]

ByteMyzer, you have way too much time on your hands.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[cariengon]

I was thinking the same thing! I want ByteMyzers job!!

 

[ByteMyzer]

Hehe, would that my job DID afford me spare time.

The above tests I performed took me all of five minutes on my break.

 

[JonFer]

ByteMyzer - were you updating a single field in each record? I realize that IIf is inefficient and the criteria query would be faster for a single field. However, the request was for updating many fields in a record and I was thinking of more like >200,000 large records. The comparison should have been running 100 criteria queries (updates) on 100 non-indexed fields versus a single update query with 100 IIf's. It may still be faster with the criteria queries because IIf is inefficient but I wouldn't say for sure unless I tested it.

I was also thinking you could do something like the update below which would return NULL when the value in a field was empty (length = 0) and leave it unchanged otherwise. This avoids IIf although it still updates every field on every record.

Set Field = [Field] & Left(8/Len([Field]),0)


John

http://www.interautomateconsulting.com