Re: Running Update Problem

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 :) Received on Fri Jun 22 2007 - 14:04:56 CDT