Re: Use SQL to UPADTE a column based on the values in colums columns

Thanks Kenny!

But I don't think you looked at the values in the example carefully!

Adam.

In article <39c75aad_2_at_News.Mountain.Net>,   "Kenny Gump" <kgump_at_mylanlabs.com> wrote:
> I'm assuming that FinishedRelNbr is a varchar2 datatype because if it
 is
> number then Oracle will strip leading zeros.
>
> update mytable
> set FinishedRelNbr = '001' || RelNbr;
>
> Kenny
> <adamvt_at_my-deja.com> wrote in message news:8q5vp6$db5
 $1_at_nnrp1.deja.com...
> > I would like to have an UPDATE to accomplish the following task in
> > plain SQL and preferably
> > without using ROWNUM, but if it must be there, then it is ok. I
 can do
> > this with PL/Sql or
> > other procedural language but I wanna know if it is possible with
 plain
> > SQL as well.
> >
> > Thanks in advance for any help!
> >
> > Acct Nbr Rel Nbr Flag FinishedRelNbr
> >
> >
> > 170005002 0000000000 1
> > 170007234 0000000000 1
> > 170004995 0170004995 0
> > 971051296 0000000000 1
> > 971051318 0971051318 0
> > 140010154 0000000000 1
> > 140003344 0140003344 0
> > 294007840 0000000000 1
> > 292016168 0292016168 0
> >
> >
> >
> >
> >
> > The RelNbr will be all zeros where you see a flag of "1".
> > We want to update the FinishedRelNbr column with the RelNbr data
> > (adding a 001 on the front of the number).
> >
> > Acct Nbr Rel Nbr Flag FinishedRelNbr
> > 170005002 0000000000 1 0010170004995
> > 170007234 0000000000 1 0010170004995
> > 170004995 0170004995 0 0010170004995
> > 971051296 0000000000 1 0010971051318
> > 971051318 0971051318 0 0010971051318
> > 140010154 0000000000 1 0010140003344
> > 140003344 0140003344 0 0010140003344
> > 294007840 0000000000 1 0010292016168
> > 292016168 0292016168 0 0010292016168
> >
> > And here is the SQL to ease your job a bit:
> >
> > DROP TABLE the_table;
> >
> > create table the_table (AcctNbr VARCHAR2(20), RelNbr VARCHAR2(20),
 Flag
> > CHAR(1), FinishedRelNbr VARCHAR2(20),
> > CONSTRAINT AcctNbr_PK PRIMARY KEY (AcctNbr));
> >
> > INSERT INTO the_table
> > (AcctNbr, RelNbr, Flag, FinishedRelNbr) VALUES
> > (170005002,'0000000000', 1, NULL);
> > INSERT INTO the_table
> > (AcctNbr, RelNbr, Flag, FinishedRelNbr) VALUES
> > (170007234,'0000000000', 1, NULL);
> > INSERT INTO the_table
> > (AcctNbr, RelNbr, Flag, FinishedRelNbr) VALUES
> > (170004995, '0170004995', 0, NULL);
> > INSERT INTO the_table
> > (AcctNbr, RelNbr, Flag, FinishedRelNbr) VALUES
> > (971051296, '0000000000', 1, NULL);
> > INSERT INTO the_table
> > (AcctNbr, RelNbr, Flag, FinishedRelNbr) VALUES
> > (971051318, '0971051318', 0, NULL);
> > INSERT INTO the_table
> > (AcctNbr, RelNbr, Flag, FinishedRelNbr) VALUES
> > (140010154, '0000000000', 1, NULL);
> > INSERT INTO the_table
> > (AcctNbr, RelNbr, Flag, FinishedRelNbr) VALUES
> > (140003344, '0140003344', 0, NULL);
> > INSERT INTO the_table
> > (AcctNbr, RelNbr, Flag, FinishedRelNbr) VALUES
> > (294007840, '0000000000', 1, NULL);
> > INSERT INTO the_table
> > (AcctNbr, RelNbr, Flag, FinishedRelNbr) VALUES
> > (292016168, '0292016168', 0, NULL);
> >
> > COMMIT;
> > SELECT * from the_table;
> >
> > Adam Tadj
> > adamt_at_hsltd.com
> >
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Sep 19 2000 - 09:26:55 CDT