Running Update Problem
This is a discussion on Running Update Problem within the Oracle Miscellaneous forums, part of the Oracle Database category; --> Greeting, I have a table say t1 with structure as (dummy) t1 id id1 id2 1 0 -1 2 ...
| |||||||
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
04-08-2008, 12:04 PM
| ||||
| ||||
 Running Update Problem 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      |
|
04-08-2008, 12:04 PM
| |||
| |||
| Re: Running Update Problem On Jun 22, 10:59 pm, "pankaj_wolfhun...@yahoo.co.in" <pankaj_wolfhun...@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 Sorry, DB version info: Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production PL/SQL Release 9.2.0.8.0 - Production and possible I want to achieve this in single sql. |
|
04-08-2008, 12:04 PM
| |||
| |||
| Re: Running Update Problem Comments embedded. On Jun 22, 12:59 pm, "pankaj_wolfhun...@yahoo.co.in" <pankaj_wolfhun...@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 |
|
04-08-2008, 12:05 PM
| |||
| |||
| Re: Running Update Problem On 22 jun, 14:59, "pankaj_wolfhun...@yahoo.co.in" <pankaj_wolfhun...@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  |
|
04-08-2008, 12:05 PM
| |||
| |||
| Re: Running Update Problem On Jun 23, 12:04 am, "Chris L." <diver...@uol.com.ar> wrote: > On 22 jun, 14:59, "pankaj_wolfhun...@yahoo.co.in" > > > > > > <pankaj_wolfhun...@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 |
|
04-08-2008, 12:06 PM
| |||
| |||
| Re: Running Update Problem On 23 jun, 06:03, "pankaj_wolfhun...@yahoo.co.in" <pankaj_wolfhun...@yahoo.co.in> wrote: > On Jun 23, 12:04 am, "Chris L." <diver...@uol.com.ar> wrote: > > > > > > > On 22 jun, 14:59, "pankaj_wolfhun...@yahoo.co.in" > > > <pankaj_wolfhun...@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; / |
|
04-08-2008, 12:06 PM
| |||
| |||
| Re: Running Update Problem Chris L. wrote: > On 23 jun, 06:03, "pankaj_wolfhun...@yahoo.co.in" > <pankaj_wolfhun...@yahoo.co.in> wrote: >> On Jun 23, 12:04 am, "Chris L." <diver...@uol.com.ar> wrote: >> >> >> >> >> >>> On 22 jun, 14:59, "pankaj_wolfhun...@yahoo.co.in" >>> <pankaj_wolfhun...@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@x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org |
|
04-08-2008, 12:06 PM
| |||
| |||
| Re: Running Update Problem On Jun 25, 7:16 pm, "Chris L." <diver...@uol.com.ar> wrote: > On 23 jun, 06:03, "pankaj_wolfhun...@yahoo.co.in" > > > > > > <pankaj_wolfhun...@yahoo.co.in> wrote: > > On Jun 23, 12:04 am, "Chris L." <diver...@uol.com.ar> wrote: > > > > On 22 jun, 14:59, "pankaj_wolfhun...@yahoo.co.in" > > > > <pankaj_wolfhun...@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 |
|
04-08-2008, 12:06 PM
| ||||
| ||||
| Re: Running Update Problem On Jun 25, 8:08 pm, "pankaj_wolfhun...@yahoo.co.in" <pankaj_wolfhun...@yahoo.co.in> wrote: > On Jun 25, 7:16 pm, "Chris L." <diver...@uol.com.ar> wrote: > > > > > > > On 23 jun, 06:03, "pankaj_wolfhun...@yahoo.co.in" > > > <pankaj_wolfhun...@yahoo.co.in> wrote: > > > On Jun 23, 12:04 am, "Chris L." <diver...@uol.com.ar> wrote: > > > > > On 22 jun, 14:59, "pankaj_wolfhun...@yahoo.co.in" > > > > > <pankaj_wolfhun...@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. |
Linear Mode
