Resetting Line Number field - HELP!!! 1

[Darrel77]

I'm having a severe brain-freeze here, guys. Here's my dilemma: I have over 350 rows in my ADDRESS table in which a dataload has gone wrong.

Over 350 rows were inserted into the ADDLINE table with no respect for the LINENBR value. And as we all know, if the line numbers aren't set to 1 and 2 (respectively), the addresses will not print (or display) properly. So it's my job to update these rows accordingly.

Here's the catch. Each account could have only one row where the LINENBR could any number from 2 thru 5. Plus (as expected) there are accounts with more than one row with line numbers in sequential order (eg: 2,3 or 3,4 or 4,5). Now as much as I would like to do a "UPDATE ADDRLINE SET LINENBR = 1 where LINENBR = 2, SET LINENBR = 2 WHERE LINENBR = 3", this will not help me where I have single rows where LINENBR = 3.

Is there some type of magic I can work in SQL to get this to run properly, or will I have to manually separate the data set and run separate update queries for each set?

I'm thinking the best way to get this done to create a SQL script in which for each account get the lines in the order they're currently in. Then loop through each line and reset the line number. But I have no idea how to do this for both my single and double line instances.

Please help!!!

Thanks, Darrel

 

[swampBoogie]

create table a(a int, l int, primary key(a,l));

insert into a values(1,2);
insert into a values(1,3);

insert into a values(2,1);
insert into a values(2,2);

insert into a values(3,3);
insert into a values(3,4);
insert into a values(3,5);

update a q
  set l = l - (select min(l) from a where a.a = q.a) + 1               
where (select min(l) from a      
        where a.a = q.a) > 1;

               5 rows updated

select * from a;
       A           L
======== ===========
       1           1
       1           2
       2           1
       2           2
       3           1
       3           2
       3           3

               7 rows found

Is that what you expect or have I misunderstood anything?

 

[rac2]

Only 350 bad rows. It might be easy and faster to correct those manually. But what fun is that.

How many lines do I have for address X? Hope you have a column that ties together the rows for one address, call it address_id. This tells you which addresses have one line.

SELECT address_id, COUNT(*) AS "number_of_lines"
FROM ADDRLINE
GROUP BY address_id
HAVING COUNT(*) = 1

You could change the LINENBR to one for all the one-liners.

UPDATE ADDRLINE SET
      LINENBR = 1
WHERE address_id IN 
( 
  SELECT s.address_id
  FROM (
         SELECT address_id, COUNT(*) AS "number_of_lines"
         FROM ADDRLINE
         WHERE address_id = address_id
         GROUP BY address_id
         HAVING COUNT(*) = 1
        ) s
)

There may be other ways to write this query depending on the RDBMS.


So that is not too bad. Now how to find the erroneous pairs of rows numbered {(2,3),(3,4)(5,6), ...}. Possibly there is a JOIN that would have rows for the (2,3) pairs; and a different JOIN that would have rows for the (3,4) pairs. These could be used to identify addresses to update to (1,2). The JOIN would replace the subquery above.

UPDATE ADDRLINE SET
      LINENBR = 1
WHERE address_id IN 
( 
   SELECT s.address_id
   FROM ADDRLINE a
   JOIN ADDRLINE b ON
         a.address_id = b.address_id
         AND a.LINENBR = 2
         AND b.LINENBR = 3
        ) s
)
   AND LINENBR = 2

Modify this to change 3 to 2.
Repeat for all pairs.


Or you could write a procedure using a CURSOR. The cursor would be defined for a straightforward query that orders the rows of ADDRLINE. Then step through the rows with logic to identify whether and how the LINENBR needs to be updated.


But as I said by the time you develop these methods you could have done it manually in Enterprise Manager.

 

[Darrel77]

Thanks to you both.

I decided to go with swampBoogie's tip. It worked like a charm. Thanks again.

- Darrel