Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server ASP
SQL Server ASP Discussions about ASP programming with Microsoft's SQL Server. For more ASP forums, see the ASP forum category.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server ASP section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old March 17th, 2006, 12:47 PM
Authorized User
Join Date: Sep 2004
Posts: 62
Thanks: 0
Thanked 1 Time in 1 Post
Default Moving Columns over on the fly with delete query

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.

Similar Threads
Thread Thread Starter Forum Replies Last Post
php/mysql delete button and delete query dungey PHP Databases 17 April 11th, 2009 12:24 PM
getting a query rows as columns raamts SQL Language 1 February 24th, 2005 04:06 AM
Moving columns i ListView problems Skorpa C# 3 May 18th, 2004 01:43 AM
SQL Query to Convert Columns into Rows Niaz SQL Server 2000 2 April 20th, 2004 01:36 AM
total columns in sql query nlicata SQL Server ASP 1 August 4th, 2003 06:33 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.