Re: Running Update Problem

From: <pankaj_wolfhunter_at_yahoo.co.in>
Date: Sat, 23 Jun 2007 02:03:05 -0700
Message-ID: <1182589385.129778.119300@n60g2000hse.googlegroups.com>

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 Received on Sat Jun 23 2007 - 04:03:05 CDT