How to change IDENTITY Key from 30361 to 29334

The follow query competes successfully but it doesn't change anything.
Used SQL Query Analyzer "The command(s) completed successfully."
ID 29334 doesn't exist, was deleted.

**BEGIN CODE**
Set identity_insert [Mantoux]  ON

Go

--The following completed successfully, but didn't change anything.
            UPDATE    dbo.Mantoux
            SET PK_Mantoux = 29334
            WHERE (((PK_Mantoux)=30361))

-- --The following completed successfully, but didn't change anything.
--            UPDATE    dbo.Mantoux
--            SET dbo.Mantoux.ID = 29334
--            WHERE (((dbo.Mantoux.ID)=30361))

-- --Following didn't work, parse didn't like anything after SET
--            INSERT INTO [Mantoux]([ID])
--            SELECT [ID]
--            SET '29334'   --Mantoux.ID = '29334'
--            WHERE [Manatoux].[ID] = '30361'

GO

SET IDENTITY_INSERT [Mantoux] OFF

Go
**END CODE**

Thanks in advance.
LVL 1
Suburb-ManAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sasapopovicCommented:
Hi,

First try this:

SELECT * FROM dbo.Mantoux
WHERE dbo.Mantoux.ID = 30361

If that query returns one row then you should execute this script:

UPDATE    dbo.Mantoux
           SET dbo.Mantoux.ID = 29334
           WHERE dbo.Mantoux.ID = 30361

If you don't get any row when you execute the SELECT statement then UPDATE also can not show you that more then 0 rows are affected because it didn't have anything to update.

I hope this will help you.
0
Suburb-ManAuthor Commented:
SELECT finds record, 30361 Does Exist.

UDATE query returns:
Cannot update identity column 'ID'.
0
sasapopovicCommented:
I forgto to mention that you have to use the: Set identity_insert ON
Your SQL should look like:

Set identity_insert [Mantoux]  ON

UPDATE    dbo.Mantoux
           SET dbo.Mantoux.ID = 29334
           WHERE dbo.Mantoux.ID = 30361

SET IDENTITY_INSERT [Mantoux] OFF

After that try this:

SELECT * FROM dbo.Mantoux
WHERE dbo.Mantoux.ID = 29334

SELECT * FROM dbo.Mantoux
WHERE dbo.Mantoux.ID = 30361

And you will see if update was successful. You should also check if your statement is executed inside of Transaction that is not commited. If that is the case, you will see 1 row affected message but change will not be applied yet.

I hope this will help you.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Suburb-ManAuthor Commented:
Are you kidding?
Did you read the code in my original question?
Everything you offered is already there!

BTW:
  SELECT * FROM dbo.Mantoux
  WHERE     (ID = 29334) OR (ID = 30361)
  Only one record found "30361".
  No Records found with ID 29334.

Again, I am running the code from SQL Query Analyzer.
0
sasapopovicCommented:
No, I'm not kidding and I read all that you wrote. I saw you had 3 different approaches for update and I wrote you how your SQL should look like. I think you maybe have open transaction that is not commited or something like that because that query looks good.

Maybe I missed something but I think the problem is not in your UPDATE statement.
0
Suburb-ManAuthor Commented:
Sorry I had to ask, unfortunatly I've had some very poor responses in other questions.

OK...Open Transactions...hmm
So you're suggesting I do this after hours when everyone out of the DB and then try the code?

Maybe it will change the error message:
  Server: Msg 8102, Level 16, State 1, Line 1
  Cannot update identity column 'ID'.   --BECAUSE SOME-BODY/THING HAS THE RECORD SET OPEN?
0
randeepsCommented:
If I am not mistaken you cannot change/update existing values of Identity columns... but you can insert your own using Set identity_insert [Mantoux]  ON


0
randeepsCommented:
what a load of crap... this has nothing to do with transactions.


you simply cannot update identity columns... straight and simple.
0
Suburb-ManAuthor Commented:
So could I insert a copy of record with ID of 30361 using/as 29334?
0
randeepsCommented:
YES! ... if it is not being used by some other record.
0
Suburb-ManAuthor Commented:
I don't quite know how to do this, here is my best guess.
please help

**START**
Set identity_insert [Mantoux]  ON

Go

INSERT INTO Mantoux(ID, CustomerID, MantouxDose, MantouxDate, MautouxGivenBy, MantouxGivenByID, MantouxLot, MantouxDateRead, MantouxResulttext,
                      MantouxResult_yn, MantouxReadBy, MantouxReadByID, MntxCxrDate, MntxConsultDate, UserPC, CreateDate, MantouxNote, MantouxResult)
SELECT      ID='29334', CustomerID, MantouxDose, MantouxDate, MautouxGivenBy, MantouxGivenByID, MantouxLot, MantouxDateRead, MantouxResulttext,
                      MantouxResult_yn, MantouxReadBy, MantouxReadByID, MntxCxrDate, MntxConsultDate, UserPC, CreateDate, MantouxNote, MantouxResult
FROM      dbo.Mantoux
WHERE      [Manatoux].[ID] = '30361'

Go

-- Update Clients
--       Set ContactFirstName = (ContactFirstName + '_**'), Notes = (Notes + '** Restored Dupe **')
--       Where [Clients].[CustomerID] = '74459'
-- Go

SET IDENTITY_INSERT [Mantoux] OFF

Go
**END**
0
Suburb-ManAuthor Commented:
How do I SET record with ID '30361' to be inserted as '29334'?
0
imran_fastCommented:
hi Suburb-Man,
Can i ask you onething why you want to update id column?
if it is a rare case and you want to do it once in a while then
From the enterprise manager go to the design table for table Mantoux and change the identity to no
and then Change  the column to be non identity and then update the value

 UPDATE    dbo.Mantoux
SET dbo.Mantoux.ID = 29334
go
From the enterprise manager go to the design table for table Mantoux and change the identity to yes

second method is create the copy with different id and delete the orignal record i.e. i am taking your code from above and modifying it

Set identity_insert [Mantoux]  ON
Go
INSERT INTO Mantoux(ID, CustomerID, MantouxDose, MantouxDate, MautouxGivenBy, MantouxGivenByID, MantouxLot, MantouxDateRead, MantouxResulttext,
                      MantouxResult_yn, MantouxReadBy, MantouxReadByID, MntxCxrDate, MntxConsultDate, UserPC, CreateDate, MantouxNote, MantouxResult)
SELECT     '29334', CustomerID, MantouxDose, MantouxDate, MautouxGivenBy, MantouxGivenByID, MantouxLot, MantouxDateRead, MantouxResulttext,
                      MantouxResult_yn, MantouxReadBy, MantouxReadByID, MntxCxrDate, MntxConsultDate, UserPC, CreateDate, MantouxNote, MantouxResult
FROM     dbo.Mantoux
WHERE     [Manatoux].[ID] = '30361'
Go
SET IDENTITY_INSERT [Mantoux] OFF
Go
DELETE FROM dbo.Mantoux WHERE     [Manatoux].[ID] = '30361'
Go
 
 
0
randeepsCommented:
Set identity_insert [Mantoux]  ON

INSERT INTO Mantoux(ID, CustomerID, MantouxDose, MantouxDate, MautouxGivenBy, MantouxGivenByID, MantouxLot, MantouxDateRead, MantouxResulttext,
                      MantouxResult_yn, MantouxReadBy, MantouxReadByID, MntxCxrDate, MntxConsultDate, UserPC, CreateDate, MantouxNote, MantouxResult)

SELECT     '29334', CustomerID, MantouxDose, MantouxDate, MautouxGivenBy, MantouxGivenByID, MantouxLot, MantouxDateRead, MantouxResulttext,
                      MantouxResult_yn, MantouxReadBy, MantouxReadByID, MntxCxrDate, MntxConsultDate, UserPC, CreateDate, MantouxNote, MantouxResult
FROM     dbo.Mantoux
WHERE     [Manatoux].[ID] = '30361'


SET IDENTITY_INSERT [Mantoux] OFF
0
randeepsCommented:
same solution as imran... so ignore my solution above
0
DonKronosCommented:
randeeps, it is NOT the same solution!!!  You correctly removed the GO statements.  When QA hits a GO it is like starting a new set of statements.  So, with the GO statements in there, the identity_insert is set to ON and the the GO statement resets it to OFF!!!!

Try this example.

declare @temp int
GO
SET @temp = 1

QA will give you the error "Must declare the variable '@temp'".
0
randeepsCommented:
@donKronos - you are right ... i overlooked this in imran's solution... thanks for pointing out.

@Suburb-Man  - if you have resolved your issue, then pls close this question by awarding points to the solution you have used.

0
Suburb-ManAuthor Commented:
Sorry I'm just now getting a chance to read this, it been a Monday from h#__!
I have a project due tomorrow morning that takes higher priority so it looks like Wednesday before I get to this.
BTW (imran_fast ) The reason is: printed documentation with 29334 that was deleted by mistake when it should have just been modified; instead they recreated it in 30361.  I need the printed documentation to match.

If I turn identity off and on for the ID column, it won't change the ID values for existing records?
It won't re-write the values in the ID column?  Because ID is a referential (Florien key) column with other tables.


0
imran_fastCommented:
<< Because ID is a referential (Florien key) column with other tables.>>
then go to the design mode for Mantoux in enterprise manager .
turn identity off, enable cascade update,
 update the column using query Analyzer o enterprise manager
 UPDATE    dbo.Mantoux
SET dbo.Mantoux.ID = 29334 where id =30361
go
enable the identity in the design mode disable the cascade update.

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Suburb-ManAuthor Commented:
Thank You

Great Job!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.