hi guys,
I just seem to have pl/sql writer's block here. I have the following set up on
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
create table foo(id number, invoice_number number, batch_number number);
insert into foo values (1,1,1);
insert into foo values (2,1,1);
insert into foo values (3,1,1);
insert into foo values (4,2,2);
insert into foo values (5,2,2);
insert into foo values (6,1,0);
insert into foo values (7,1,0);
insert into foo values (8,1,0);
insert into foo values (9,2,0);
insert into foo values (10,2,0);
commit;
create sequence foo_batch start with 3;
I would like to: update all the rows that have a batch_Number = 0 to have their batch_number equal to a new sequence number for each set of invoice_number.
here's the current data:
SQL> select *
2 from foo;
ID INVOICE_NUMBER BATCH_NUMBER
---------- -------------- ------------
1 1 1
2 1 1
3 1 1
4 2 2
5 2 2
6 1 0
7 1 0
8 1 0
9 2 0
10 2 0
10 rows selected.
for example assuming that the sequence number created will start from 3 I would like the table to now look like:
SQL> select *
2 from foo;
ID INVOICE_NUMBER BATCH_NUMBER
---------- -------------- ------------
1 1 1
2 1 1
3 1 1
4 2 2
5 2 2
6 1 3
7 1 3
8 1 3
9 2 4
10 2 4
10 rows selected.
I just can't figure out how to do this in a single SQL statement. I started initially by bulk collecting distinct invoice_number and the new sequence where batch_number = 0 and then updating but I don't think that's the best way.
I have a feeling this should be easy but for some reason my brain is not working this morning.
any pointers in the right direction would be appreciated.