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

From: Jusung Yang <JusungYang_at_yahoo.com>
Date: 9 Oct 2002 21:49:56 -0700
Message-ID: <130ba93a.0210092049.79e0f415@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 Wed Oct 09 2002 - 23:49:56 CDT