Sql Server 2005 Swap ID Numbers

I have a table that has two columns "name" and "ID". where ID is not null.

I am trying to create a stored prcedure to swap the IDs around for two names when imputed here is what i have so far.

CREATE PROCEDURE dbo.procName 
    @OldName NVARCHAR(128), 
    @NewName NVARCHAR(128)
AS

DECLARE @NewId0 INT,
    @NewId1 INT,
    @OldId0 INT, 
    @OldId1 INT, 
    @Number INT


SELECT @NewId0 = ID FROM Table1 WHERE [Name] = @NewName
SELECT @NewId1 = ID FROM Table1 WHERE [Name] = @NewName
SELECT @OldId0 = ID FROM Table1 WHERE [Name] = @OldName
SELECT @OldId1 = ID FROM Table1 WHERE [Name] = @OldName
SELECT @Number = 0 

UPDATE Table1 SET ID = @Number WHERE ID = @NewId0 
U开发者_开发百科PDATE Table1 SET ID = @NewId1 WHERE ID = @OldId0 
UPDATE Table1 SET ID = @OldID1 WHERE ID = @NewID0 


Go

All I get is the first name to have the value 0.

I think my logic is correct but it doesn't seem to be working is there something that I am missing?

Try something like

UPDATE  Table1
SET     ID =    CASE 
                    WHEN ID = @NewId 
                        THEN @OldId
                    ELSE @NewId
                END
WHERE   ID IN (@NewId, @OldId)

Here is a full example

DECLARE @Table TABLE(
        ID INT,
        Name VARCHAR(20)
)

INSERT INTO @Table SELECT 1,'A'
INSERT INTO @Table SELECT 2,'B'

DECLARE @NewName VARCHAR(20),
        @OldName VARCHAR(20)

SELECT  @NewName = 'A',
        @OldName = 'B'

DECLARE @NewId INT, 
    @OldId INT


SELECT @NewId = ID FROM @Table WHERE [Name] = @NewName 
SELECT @OldId = ID FROM @Table WHERE [Name] = @OldName  

SELECT  *
FROM    @Table

UPDATE  @Table
SET     ID =    CASE 
                    WHEN ID = @NewId 
                        THEN @OldId
                    ELSE @NewId
                END
WHERE   ID IN (@NewId, @OldId)

SELECT  *
FROM    @Table

This script works if the ID is not a primary Key

CREATE PROCEDURE dbo.procName @OldName NVARCHAR(128), @NewName NVARCHAR(128) As DECLARE @NewId INT, @OldId INT

SELECT @NewId = ID FROM Table1 WHERE [Name] = @NewName
SELECT @OldId = ID FROM Table1 WHERE [Name] = @OldName

UPDATE Table1 SET ID = CASE
WHEN ID = @NewId
THEN @OldId ELSE @NewId END WHERE ID IN (@NewId, @OldId)

SELECT * FROM Table1 go

Your current code fails because:

• You Look for @NewId0, and set it to @Number (which is 0)
• You then look for @OldId0, and set it to @NewId1
• You then look for @NewId0 again... but two lines back, you set it to 0, so it's not in the table any more

I like @astander's solution (upvoted), saved me writing it out myself.

BUT... your comment, "...ID is a Primary Key", raises all kinds of red flags. You really, really don't want to change primary key values [insert long discussion from past classes about primary keys and relational integrity here]. Figure out why you think you need to do it, and then figure out another way to implement that business requirement, such as:

• Don't change the IDs, change everything else (name, description, cost, whatever)
• Create completely new entries and drop (or mark as completed, discareded, or junk) the old ones
• Implement some clever logic based on the underlying business requirements.

under stand the concerns with changing PKs but it has to be done as it would take too long to change all the other stuff. That said I have come up with an answer. This is only the first part of a long script so again its not perfect:

    CREATE PROCEDURE dbo.ID @OldName NVARCHAR(128), 
                        @NewName NVARCHAR(128)

AS
DECLARE @NewId INT,
        @OldId INT

CREATE TABLE TmpTable (ID INT,Name NVARCHAR(128)) 

INSERT INTO TmpTable (Name,ID) 
VALUES (@NewName,(SELECT ID FROM Table1 WHERE [Name] = @NewName));

INSERT INTO TmpTable (Name,ID)
VALUES(@OldName,(SELECT ID FROM Table1 WHERE [Name] = @OldName))

UPDATE Table1 SET ID = 11 WHERE [NAME]  = @NewName
UPDATE Table1 SET ID = 10 WHERE [NAME]  = @OldName

UPDATE Table1 SET ID = (SELECT ID FROM TmpTable where Name = @NewName)WHERE [Name] = @OldName
UPDATE Table1 SET ID = (SELECT ID FROM TmpTable where Name = @OldName) WHERE [Name] = @NewName

DROP TABLE TmpTable
go

"11" and "10" were selected as they are not in the table my next mission will be to query table1 and for a random number that dosent exist then use that number to temprary repplace the ID before updating the new ones.

Thanks