Re: I need help with an SQL Update query...

Thanks Jusung, I get the picture. I know there are several ways to do this, but my ways are usually the long way. I probably would have thrown the ID in another table and joined by ID and selected 1 row with the value.I was just wondering if there was a short way.

Thanks again.

P.S. Thak you too, Daniel, for your hint. Nahhh, it's not homework. The only time I have to go to school is to pick up my son.

JusungYang_at_yahoo.com (Jusung Yang) wrote in message news:<130ba93a.0210092049.79e0f415_at_posting.google.com>...
> You can easily do that with PL/SQL. If you insist on using SQL, here
> is a little trick you can try:
>
> Update tablea set my_values=0 where orwid in (
> select rid from (select rowid rid, row_number() over
> (partition by ID order by ID, my_values) rn
> from tablea
> )
> where rn>1);
>
>
>
> I did not try it out on your data. But you get the idea.
>
>
> - Jusung Yang
>
>
> RappaJ_at_nycha.nyc.gov (Joe) wrote in message news:<ab8e8633.0210091231.570c1853_at_posting.google.com>...
> > Hi,
> >
> > Lets say that I had a table called TABLEA with 2 columns called ID and
> > MY_VALUES as shown in the sample below.
> >
> > ID MY_VALUES
> > == =========
> > AA 100
> > AA 100
> > AA 100
> > AA 100
> > AA 100
> > BB 222
> > BB 222
> > BB 222
> > BB 222
> > CC 999
> > CC 999
> > CC 999
> > DD 100
> > DD 100
> > DD 100
> >
> > Is there a simple SQL update command that I can use to have the output
> > look like this below? Basically,I want to keep the first value of
> > MY_VALUES, and zero out the rest for that ID.
> >
> > ID MY_VALUES
> > == =========
> > AA 100
> > AA 0
> > AA 0
> > AA 0
> > AA 0
> > BB 222
> > BB 0
> > BB 0
> > BB 0
> > CC 999
> > CC 0
> > CC 0
> > DD 100
> > DD 0
> > DD 0
> >
> > Thanks,
> > Stumped
Received on Thu Oct 10 2002 - 07:02:54 CDT