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

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.
Received on Tue Sep 19 2000 - 07:23:10 CDT