Re: Running Update Problem

Comments embedded.
On Jun 22, 12:59 pm, "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.
>

Then you use a sequence.

> Means, here the min(id2) is -5, so all id1=0 record should get updated
> starting with -6, -7, -8 and so on.
>

Again, you use a sequence.

> Output like
>
> id id1 id2
> 1 -6 -1
> 2 -7 -3
> 3 8 -4
> 4 -8 -5
>
> Can anyone help me build this logic?

Use a sequence:

SQL> select *
  2 from id;

        ID ID1 ID2

---------- ---------- ----------
         1          0         -1
         2          0         -3
         3          0         -4
         4          0         -5

SQL> create sequence myseq start with -6 increment by -1 nocache nocycle nomaxvalue;

Sequence created.

SQL> update id
  2 set id1=myseq.nextval
  3 where id1 = 0;

4 rows updated.

SQL> select *
  2 from id;

        ID ID1 ID2

---------- ---------- ----------
         1         -6         -1
         2         -7         -3
         3         -8         -4
         4         -9         -5

SQL>
>
> Help would be appreciated
>
> TIA
David Fitzjarrell Received on Fri Jun 22 2007 - 13:29:29 CDT