Re: Sorting update?

I like it, but it would not go down well if you ran it against all the employees at Oracle. Semi-product join.

How about:

update emp t1
set pos = rownum
where (rowid,salary) in (select rowid,salary

               from emp
               group by salary,rowid)

Ok, this is naughty because it uses a group by to do a sort. Don't get joint positions, but if you make sure every in the company is on a different salary you won't have the problem.

Hands up all those who can guess (without using explain) why salary needs to be included in the in clause?

Thomas Kyte wrote:

> A copy of this was sent to Thorsten Koch <torsti_engy_at_my-deja.com>
> (if that email address didn't require changing)
> On Tue, 21 Sep 1999 17:55:08 GMT, you wrote:
>
> >Hi folks!
> >
> >Let there be table EMP with
> >POS NUMBER
> >NAME VARCHAR2
> >SAL NUMBER
> >
> >and the data
> >POS NAME SAL
> >----- ------ -----
> >1 A 1000
> >2 C 500
> >3 B 700
> >
> >Now I want to update the table in a way that its sorted by salaries, so
> >that it looks like this:
> >
> >POS NAME SAL
> >----- ------ -----
> >1 C 500
> >2 B 700
> >3 A 1000
> >
> >That is, I would like to have an update-statement like
> >
> >update emp
> >set pos=(select rownum
> > from emp
> > order by sal);
> >
>
> this is a *little* different from what you asked for in that if 2 people have
> the SAME salary -- i'll assign them the same ranking. For example:
>
> tkyte_at_8.0> update emp
> 2 set pos = ( select count(*)
> 3 from emp b
> 4 where b.sal < emp.sal );
>
> 14 rows updated.
>
> 1* select empno, pos, sal from emp order by pos
> tkyte_at_8.0> /
>
> EMPNO POS SAL
> ---------- ---------- ----------
> 7369 0 800
> 7900 1 950
> 7876 2 1100
> 7521 3 1250
> 7654 3 1250
> 7934 5 1300
> 7844 6 1500
> 7499 7 1600
> 7782 8 2451
> 7698 9 2850
> 7566 10 2975
> 7788 11 3000
> 7902 11 3000
> 7839 13 5000
>
> That has 2 #11's because they both make 3,000 (note that there is NO number 12
> -- just 2 #11's).
>
> It does a nice ranking for you if thats what you want... Some people might say
> this is 'un-natural' as there really isn't any 'order' to tuples in a database
> table but it can be useful to do real world things like generate top-n reports
> and rankings....
>
> >Unfortunately, it is not possible to use "order by" in an update
> >statement.
> >
> >Can someone advise me how to perform this task? I would be glad to
> >receive a few different solutions, because not all solutions may fit to
> >my special application.
> >
> >Many thanks,
> >
> >Thorsten
> >
> >
> >Sent via Deja.com http://www.deja.com/
> >Share what you know. Learn what you don't.
>
> --
> See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
> Current article is "Part I of V, Autonomous Transactions" updated June 21'st
>
> Thomas Kyte tkyte_at_us.oracle.com
> Oracle Service Industries Reston, VA USA
>
> Opinions are mine and do not necessarily reflect those of Oracle Corporation
Received on Tue Sep 21 1999 - 17:16:28 CDT