Re: Slow Update!!

Thanks Hitman for your prompt reply ..

Why I choose the Correlated update was that the condition for query can change as it is for telecom industry.

In order to find if someone has applied for multiple phones then one base check can be that either name,address,city matches or contact number matches.

 Id, Name, City, Contact Group_Id

• ===== ==== ======= ====== 1 J LA 1 2 J LA 2 3 J LA 3 4 J LA 4 5 J LA 5 6 J LA 6

 7   J          LA1     7
 8   J          LA1     1

 9   J          LA2     1

10  Mr. J    LA        10

I think connecting 9th ID to 1st group may not be possible here using over (partition by ) which is an excellent information from you. I never gave it a thought. Thanks again for improving my repositry.

If Like is to be implemented then Mr. J is equivalent to J so 10 should be connected to 1st Group.

Any Help or sugesstion is greatly appreciated.

With Warm regards
Jatinder Singh

hitman wrote:
> jsfromynr wrote:
> > Basically what I am doing is
> > What I want is that first six rows form group One
> > and Second group is of next three rows and so on.
>
> Please don't use rowid. It's very wrong.
> Since the order in an oracle table is unpredictable, you must have a
> column which will guarantee that your Name and City will be in the
> order you want. You can use a sequence for that.
>
> So you should have something like:
>
> Id, Name, City, Group_Id
> == ===== ==== =======
> 1 J LA
> 2 J LA
> 3 J LA
> 4 J LA
> 5 J LA
> 6 J LA
> 7 J LA1
> 8 J LA1
> 9 J LA1
>
> Then you can derive Group_Id from a query like
>
> select id, name, city, first_value(id) over (partition by name, city
> order by id) grp_id
> from tablex
>
> Id, Name, City, grp_id
> == ===== ==== =======
> 1 J LA 1
> 2 J LA 1
> 3 J LA 1
> 4 J LA 1
> 5 J LA 1
> 6 J LA 1
> 7 J LA1 7
> 8 J LA1 7
> 9 J LA1 7
>
> ...and do the update accordingly.
>
> DS
Received on Thu Jul 06 2006 - 09:32:56 CDT