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

On Thu, 2007-09-06 at 14:13 +0800, Ow Mun Heng wrote:
> I have a table in PG, Pulled from SQL Server using Perl DBI (w/o using
> chopblanks) and have ended up with a column where the "space" is being
> interpreted as a value.
>
> eg:
>
> "ABC " when it should be "ABC"
>
> this is being defined as varchar(4)
>
> I've already pull the relevent columns with
>
> create foo as select unique_id, rtrim(number) from org_column
>
> I've tried to do the update using
>
> update org_column set number = foo.number where foo.unique_id =
> org_column=unique_id.
>
> The update is taking a few hours and still hasn't ended.
>
> I've killed it already and rolled back the changes.
>
> what's the easiest way to update these fields?
>
>
> Thanks..

Bad Form.. I know.. replying to my own post.. but..

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

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%';

of course this will need to parse it through like a couple of times,
unless I use some regex magic etc..

but anyway.. problem solved and using chopblanks => 1 now..