Re: Need suggestion on how best to update 3 million rows

Ow Mun Heng wrote:
> On Thu, 2007-09-06 at 04:47 -0500, Ron Johnson wrote:
>> Maybe there's an English language "issue", or maybe I'm just
>> excessively picky, but using "number" in this context is confusing.
>
> My Bad.. hehe..
>
>> Then I agree with Alban:
>>   update table set number = trim(number);
>> or, if you need the leading spaces:
>>   update table set number = rtrim(number)
>>
>> Then:
>>   update table set number = NULL where number = '';
>
> Dag it.. I wish I didn't live on the other end of the TimeZone and
> gotten this answer before I went the "hard way"
>
> oh well.. lesson learned and that's the positive spin on it right?
>
> Thanks a bunch guys..

Two other tips for bulk-updates like this:
1. Do as many columns in one go as you can
2. Only update rows that need updating

When you've finished, a CLUSTER/VACUUM FULL can be useful too.

--
   Richard Huxton
   Archonet Ltd