I made a column called batch_no to keep track of how many records have been sent and processed.
As an example, I was told today that I needed to send 10 records. So the 10 that were chosen would be updated to batch_no = 1. I would have 90 records left, and if I were asked to choose 70, I would update the 70 records to batch_no = 2, leaving me with 20 records. Lastly, if I was asked to do 30 the next day, that would be more I have left, so I would choose 20, change batch_no to 3, and that would be it. Can you please help me write this in sql.
Below data is just an example 100 records in a table.
Unfortunately I'm using an old version 9i.
CREATE TABLE tbl_tmp
( phone_number VARCHAR2(20) batch_no NUMBER(3)
);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('515.123.4567',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('515.123.4568',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('515.123.4569',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('590.423.4567',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('590.423.4568',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('590.423.4569',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('590.423.4560',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('590.423.5567',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('515.124.4569',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('515.124.4169',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('515.124.4269',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('515.124.4369',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('515.124.4469',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('515.124.4567',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('515.127.4561',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('515.127.4562',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('515.127.4563',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('515.127.4564',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('515.127.4565',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('515.127.4566',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('650.123.1234',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('650.123.2234',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('650.123.3234',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('650.123.4234',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('650.123.5234',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('650.124.1214',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('650.124.1224',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('650.124.1334',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('650.124.1434',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('650.124.5234',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('650.124.6234',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('650.124.7234',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('650.124.8234',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('650.127.1934',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('650.127.1834',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('650.127.1734',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('650.127.1634',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('650.121.1234',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('650.121.2034',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('650.121.2019',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('650.121.1834',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('650.121.8009',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('650.121.2994',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('650.121.2874',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('650.121.2004',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('011.44.1344.429268',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('011.44.1344.467268',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('011.44.1344.429278',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('011.44.1344.619268',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('011.44.1344.429018',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('011.44.1344.129268',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('011.44.1344.345268',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('011.44.1344.478968',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('011.44.1344.498718',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('011.44.1344.987668',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('011.44.1344.486508',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('011.44.1345.429268',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('011.44.1345.929268',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('011.44.1345.829268',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('011.44.1345.729268',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('011.44.1345.629268',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('011.44.1345.529268',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('011.44.1346.129268',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('011.44.1346.229268',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('011.44.1346.329268',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('011.44.1346.529268',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('011.44.1346.629268',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('011.44.1346.729268',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('011.44.1343.929268',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('011.44.1343.829268',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('011.44.1343.729268',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('011.44.1343.629268',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('011.44.1343.529268',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('011.44.1343.329268',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('011.44.1644.429267',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('011.44.1644.429266',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('011.44.1644.429265',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('011.44.1644.429264',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('011.44.1644.429263',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('011.44.1644.429262',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('650.507.9876',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('650.507.9877',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('650.507.9878',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('650.507.9879',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('650.509.1876',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('650.509.2876',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('650.509.3876',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('650.509.4876',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('650.505.1876',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('650.505.2876',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('650.505.3876',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('650.505.4876',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('650.501.1876',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('650.501.2876',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('650.501.3876',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('650.501.4876',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('650.507.9811',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('650.507.9822',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('650.507.9833',null);
Insert into TBL_TMP (PHONE_NUMBER,BATCH_NO) values ('650.507.9844',null);
commit;