On Jun 25, 8:08 pm, "pankaj_wolfhun..._at_yahoo.co.in"
<pankaj_wolfhun..._at_yahoo.co.in> wrote:
> On Jun 25, 7:16 pm, "Chris L." <diver..._at_uol.com.ar> 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;
> > /- Hide quoted text -
>
> > - Show quoted text -
>
> Thanks Chris.
> Even I didnt came up with any idea to do it in single sql and went for
> pl/sql.
> The reason I asked for single sql was, we have records in millions
> (around 30 million) and
> achieving the update in pl/sql might affect the performance (correct
> me if i am wrong)
> Can you or anyone tell me some points to keep in mind when
> implementing the logic in pl/sql?
> (I have already looked into bulk collect feature).
> Is doing this in single sql better than using pl/sql?
>
> TIA- Hide quoted text -
>
> - Show quoted text -
Thanks Daniel. Thanks everyone.
Will get back if more queries.
Thanks again.
Received on Mon Jun 25 2007 - 11:20:33 CDT
