Re: Update Joined tables
Date: Thu, 27 Dec 2001 01:59:59 GMT
Message-ID: <zgvW7.4862$7p6.116422_at_rwcrnsc51.ops.asp.att.net>
What version of Oracle?
What update statement have you tried? What does the explain plan look like?
What indexes do you have? Have you analyzed the table and indexes?
I am thinking:
update table1 t1 set townCity=(select townCityNew from table2 t2 where
t1.townCity=t2.townCity and rownum<2) where
exists (select 1 from table2 t2e where t2e.townCity=t1.townCity);
Hope you have an index on townCity in both tables. create index tab1_city_idx on table1(towncity) compress; create index tab2_city_idx on table2(towncity) compress;
Jim
"Dean Attewell" <xtr92978101_at_xtra.co.nz> wrote in message
news:26ef90c4.0112261215.13d59e14_at_posting.google.com...
> I have 2 tables
>
> Table1 is customer address table eg
> CustID
> Addr1
> Addr2
> TownCity
>
> The other table is
> TownCity
> TownCityNew
>
> Basically I am wanting to join the 2 TownCity fields, and replace
> table1 with teh new spelling of TownCity
>
> The reason, is we have 2million customer addresses, and we have some
> common misspellings, I want to take all of teh common mispellings and
> replace with correct spelling.
>
> But Oracle doensn't like joins with an update, and PL/SQL cursor takes
> about 4 hours, MS access can do this in 10 minutes.
>
> Can someone please restore my faith that Oracle is teh best & fastest
> database in teh world!
>
> Thanks
> Dean
Received on Thu Dec 27 2001 - 02:59:59 CET