Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

How do I increase all of my EmployeeID values by one if it is the primary key?

share|improve this question
1  
This should work, unless there are foreign key references in other tables: UPDATE dbo.table SET pk += 1; –  Aaron Bertrand Sep 23 '13 at 16:10
2  
@ Aaron Bertrand : It won't work for identity pk also... –  a1ex07 Sep 23 '13 at 16:11
2  
This question appears to be off-topic because it is about a homework question. –  RolandoMySQLDBA Sep 23 '13 at 22:11
5  
@RolandoMySQLDBA why is homework off-topic just because it's homework? It's ok to give answers to people who can't do their own jobs, but it's not ok to give answers to people who can't do their own homework? I think we can be a lot more reasonable than that. –  Aaron Bertrand Sep 24 '13 at 1:17
1  
FWIW, I voted to close as Too Broad. IMO there are too many unknowns to give a reasonably concise answer. –  Jon Seigel Sep 24 '13 at 13:37
show 5 more comments

2 Answers

I assume that this question is about how to update a bunch of rows to a value on higher without stepping on each other. If you update id=1 to id=2 in the first row while the second row (with id=2) is still there you will get a primary key violation because now you have two rows with id=2.

To prevent that type of collision you just need to start at the largest value and increase it by one. that opens up a gap for the second largest value to move into which in turn opens a gap for the third largest value to move into, and so on.

In SQL Server however, you need not worry, as the engine is taking care of that automatically:

SQL Fiddle

MS SQL Server 2008 Schema Setup:

CREATE TABLE dbo.Employee(Id INT PRIMARY KEY CLUSTERED, Name NVARCHAR(MAX));

INSERT INTO dbo.Employee(Id,Name)
VALUES(1,'John'),(2,'Jane'),(3,'Max');

Query 1:

SELECT * FROM dbo.Employee;

Results:

| ID | NAME |
|----|------|
|  1 | John |
|  2 | Jane |
|  3 |  Max |

Query 2:

UPDATE dbo.Employee
  SET Id = Id + 1;

Execution Plan

Query 3:

SELECT * FROM dbo.Employee;

Results:

| ID | NAME |
|----|------|
|  2 | John |
|  3 | Jane |
|  4 |  Max |

Query 4:

UPDATE dbo.Employee
  SET Id = Id - 1;

Execution Plan

Query 5:

SELECT * FROM dbo.Employee;

Results:

| ID | NAME |
|----|------|
|  1 | John |
|  2 | Jane |
|  3 |  Max |

If you actually follow the SQL Fiddle link, you can also see the execution plans for the two update statements. The first one looks like this:

enter image description here

There you can see fairly far to the left a Table Spool operator. That operator basically creates a copy of all rows that were updated before they get written back by the actual `Clustered Index Update' operator next to it. Because of this SQL Server is able to handle "overlapping" updates without tripping over itself.

An added benefit (and the original reason why this behavior was implemented) is that this two step approach provides Halloween Protection.

share|improve this answer
2  
The table spool is needed because the name column is a LOB type. With a non-LOB name, the sort provides sufficient phase separation for HP. The Split-Sort-Collapse optimizes logging and ensures changes are applied in an order that prevents transient key violations. –  Paul White Sep 24 '13 at 1:40
    
@PaulWhite, thanks for the clarifying comment. You are bringing up quite a few concepts in your short sentence above. Does one of you articles go into more detail? –  Sebastian Meine Sep 24 '13 at 14:07
2  
I had to check, but yes! I discuss Split-Sort-Collapse in this post and Halloween Protection in a four-part series. Conor Cunningham talks about the logging optimization in his chapter of the 2008 Internals book from MS Press. –  Paul White Sep 24 '13 at 17:10
    
@PaulWhite Is there something you haven't discussed about SQL Server? –  Kermit Sep 24 '13 at 17:21
add comment

Without more detail about the structure of your database there are a large number of variables here.

If any other table that contains the employee IDs as reference to this table and forgeign keys are properly setup and set to ON UPDATE CASCADE then simply executing UPDATE EmployeeTable SET EmployeeID = EmployeeID + 1 will do the trick - the FK relationships will update everything else as needed. Without ON UPDATE CASCADE in this case you will get errors and the statement will fail.

If you have tables that refer to the employee ID without forgeign key constraints being present then you will have to update those too with UPDATE AnotherTable SET EmployeeID = EmployeeID + 1. You should wrap all the table updates in a single transaction (with error handling to make sure the transaction is rolled back or SET XACT_ABORT ON if you don't need any finer control over that process) in order to maintain consistency (so if something fails everything is rolled back).

I would question why you would ever want to do this though. An employee identifier is generally a surrogate key (also called a pseudo key) that carries no meaning aside from identifying a given person (it could eaily be a UUID insead of a number, except for the fact it is likely used in real-world written down, so a UUID would not be convenient). See the "psuedo-key neat-freak" chapter in SQL Antipatterns for more discussion on this.

share|improve this answer
1  
I would question why you would ever want to do this though. Excellent point. Maybe he really needs a new column with this data in it. –  dcaswell Sep 24 '13 at 2:59
    
+1 million for "why would you ever want to do this"! Keys are supposed to be invariant for very good reasons. What, for example, is the legal implication of changing Alice's employeeid from 5 to 6 if Alice has a signed contract with "EmployeeID: 5" printed on it? –  Greenstone Walker Sep 24 '13 at 4:03
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.