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 [EMAIL PROTECTED] 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 // ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster