Suburb-Man
asked on
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.
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.
ASKER
SELECT finds record, 30361 Does Exist.
UDATE query returns:
Cannot update identity column 'ID'.
UDATE query returns:
Cannot update identity column 'ID'.
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.
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.
ASKER
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.
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.
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.
Maybe I missed something but I think the problem is not in your UPDATE statement.
ASKER
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?
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?
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
what a load of crap... this has nothing to do with transactions.
you simply cannot update identity columns... straight and simple.
you simply cannot update identity columns... straight and simple.
ASKER
So could I insert a copy of record with ID of 30361 using/as 29334?
YES! ... if it is not being used by some other record.
ASKER
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**
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**
ASKER
How do I SET record with ID '30361' to be inserted as '29334'?
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
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
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
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
same solution as imran... so ignore my solution above
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'".
Try this example.
declare @temp int
GO
SET @temp = 1
QA will give you the error "Must declare the variable '@temp'".
@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.
@Suburb-Man - if you have resolved your issue, then pls close this question by awarding points to the solution you have used.
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank You
Great Job!
Great Job!
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.