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

Ow Mun Heng wrote:
> I found 2 new ways to do this.
>
> option 1
> -------
>
> create table foo as select unique_id, rtrim(number) as number from foo;
> alter table add primary key...
> create index...
> drop org_table
> alter table rename...
> All this is ~10min

This only works if you don't have foreign key constraints involving that
table. Otherwise you just lost your data integrity (although I expect an
error to be thrown).

> option 2
> ========
> This I saw in the mysql archives (in my laptop).. when I say this I
> went.. WTF? This is possible?? Dang IT!
>
> update a set number=replace(number,'ABC ', 'ABC') where reptest like '%
> ABC%';

Ehrm... yes, nothing special about it. Basic SQL really ;)
But shouldn't you be using trim() or rtrim() instead?:

update table set number = trim(number)

you could probably speed that up by only querying the records that need
trimming, for example:

create index tmp_idx on table(number) where number != trim(number);
analyze table;
update table set number = trim(number) where number != trim(number);

--
Alban Hertroys
alban(at)magproductions(dot)nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede

// Integrate Your World //