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

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 Mon Sep 18 2000 - 16:00:37 CDT