[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 256
  • Last Modified:

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.
0
Suburb-Man
Asked:
Suburb-Man
  • 8
  • 6
  • 3
  • +2
1 Solution
 
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
Suburb-ManAuthor Commented:
Thank You

Great Job!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 8
  • 6
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now