Chris L. wrote:
> On 23 jun, 06:03, "pankaj_wolfhun..._at_yahoo.co.in"
> <pankaj_wolfhun..._at_yahoo.co.in> wrote:
>> On Jun 23, 12:04 am, "Chris L." <diver..._at_uol.com.ar> wrote:
>>
>>
>>
>>
>>
>>> On 22 jun, 14:59, "pankaj_wolfhun..._at_yahoo.co.in"
>>> <pankaj_wolfhun..._at_yahoo.co.in> wrote:
>>>> Greeting,
>>>> I have a table say t1 with structure as (dummy)
>>>> t1
>>>> id id1 id2
>>>> 1 0 -1
>>>> 2 0 -3
>>>> 3 8 -4
>>>> 4 0 -5
>>>> I want to:
>>>> update t1 set id1=min(id2) where id1=0.
>>>> but each id1 should get updated with one value greater than the
>>>> previous value.
>>>> Means, here the min(id2) is -5, so all id1=0 record should get updated
>>>> starting with -6, -7, -8 and so on.
>>>> Output like
>>>> id id1 id2
>>>> 1 -6 -1
>>>> 2 -7 -3
>>>> 3 8 -4
>>>> 4 -8 -5
>>>> Can anyone help me build this logic?
>>>> Help would be appreciated
>>>> TIA
>>> CREATE TABLE t1 (
>>> id NUMBER(2),
>>> id1 NUMBER(2),
>>> id2 NUMBER(2));
>>> INSERT INTO t1
>>> SELECT 1, 0, -1 FROM dual union
>>> SELECT 2, 0, -3 FROM dual union
>>> SELECT 3, 8, -4 FROM dual union
>>> SELECT 4, 0, -5 FROM dual ;
>>> UPDATE t1 SET
>>> id1=
>>> -ROWNUM+(SELECT Min(id2) FROM t1 WHERE id1=0)
>>> WHERE id1=0;
>>> This gives the expected output of your particular example.
>>> BTW "one value greater" than -5 is -4, not -6 :)- Hide quoted text -
>>> - Show quoted text -
>> Thanks David, Chris. That was helpful.
>>
>> One more point, what if I want to update id based on some group by
>> condition.
>> Suppose the structure changes to
>>
>> t1
>>
>> id id1 id2 sign
>> 1 0 -1 A
>> 2 0 -3 A
>> 3 8 -4 B
>> 4 0 -5 B
>> 5 0 -5 B
>>
>> and I would like to apply the same logic grouping on sign.
>> Output like
>>
>> id id1 id2 sign
>> 1 -6 -1 A
>> 2 -7 -3 A
>> 3 8 -4 B
>> 4 -6 -5 B
>> 5 -7 -5 B
>>
>> Something like
>>
>> UPDATE t1
>> SET id1=-ROWNUM+(SELECT Min(id2) FROM t1 WHERE id1=0)
>> WHERE id1=0
>> GROUP BY sign;
>>
>> Any idea?
>>
>> TIA
>
> Sorry I don't know enough SQL to do it in one single statement.
> Here's some PL/SQL code to do it. Basically repeats the update
> statement for every value of "sign".
>
> CREATE TABLE t1 (
> id NUMBER(2),
> id1 NUMBER(2),
> id2 NUMBER(2),
> ssign CHAR(1));
>
> INSERT INTO t1
> SELECT 1, 0, -1,'A' FROM dual union
> SELECT 2, 0, -3,'A' FROM dual union
> SELECT 3, 8, -4,'B' FROM dual union
> SELECT 4, 0, -5,'B' FROM dual union
> SELECT 5, 0, -5,'B' FROM dual ;
>
> BEGIN
> DECLARE
> CURSOR cur_signs is
> SELECT DISTINCT ssign FROM t1;
> min_value NUMBER;
>
> BEGIN
>
> SELECT Min(id2) INTO min_value FROM t1
> WHERE id1=0;
>
> FOR rrow IN cur_signs LOOP
> UPDATE t1 SET
> id1= min_value - ROWNUM
> WHERE id1=0 AND ssign=rrow.ssign;
> END LOOP;
>
> END;
> END;
> /
Even if you don't know enough SQL to do it in one statement ... this
is an implementation that has not been much good since version 8i.
Take a look at this:
http://www.psoug.org/reference/array_processing.html
And run the "SLOW_WAY" and "FAST_WAY" demos.
--
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Mon Jun 25 2007 - 10:05:34 CDT
