I am sure there is a better way of doing this than I am currently so here it goes...

I have a zipcode database that has the following columns: zipcode (pk), id1, id2, id3, etc, etc. Now all of the rows have varius columns filled in - some with no id(*) data, some with up to 8 columns of id data. When one of our affiliates cancels I remove their id from the tables in any of the id columns where it might be found - some are in id1, some id6, etc.

So my question is - is there an easy way while doing the delete query to move everything to the right of it over to the left (if applicable)? If the id exists 6 times in column 3 then I want whatever is in column 4 to move to column 3 and take its place - this effects the hierarchy.

Make sense?

Right now I do a bunch of if statements like:

select case search_id
        case Rs("dealer_id_1")
            new_dlr1 = Rs("dealer_id_2")
            new_dlr2 = Rs("dealer_id_3")
            new_dlr3 = Rs("dealer_id_4")
            new_dlr4 = Rs("dealer_id_5")
            new_dlr5 = Rs("dealer_id_6")
            new_dlr6 = null
        case Rs("dealer_id_2")
            new_dlr1 = Rs("dealer_id_1")
            new_dlr2 = Rs("dealer_id_3")
            new_dlr3 = Rs("dealer_id_4")
            new_dlr4 = Rs("dealer_id_5")
            new_dlr5 = Rs("dealer_id_6")
            new_dlr6 = null

Just to move things over - I know there is a way better way.