CODEWITHSUNDEEP
sqlsql-servert-sql
general exception
general exception

Reputation: 4332

SQL Server, renumber int column sequentially on two tables

I have the following two tables and data:-

CREATE TABLE customers
    ([id] int, [name] varchar(10), [sex] varchar(1))
;

INSERT INTO customers
    ([id], [name], [sex])
VALUES
    (1050, 'John Doe', 'M'),
    (1060, 'Jane Doe', 'F'),
    (1031, 'Joe Bloggs', 'M')
;

CREATE TABLE orders
    ([id] int, [fk] int, [product] varchar(13))
;

INSERT INTO orders
    ([id], [fk], [product])
VALUES
    (51, 1050, 'Blue car'),
    (57, 1050, 'Yellow car'),
    (43, 1060, 'Pink bus'),
    (32, 1031, 'Black pen'),
    (87, 1031, 'Orange jacket')
;

What i want to do is re-number the id column in both tables sequentially starting from 1.

The linked rows in the orders table must also be renumbered, and the foreign key in this table must match the new number in the customers table.

so the data needs to end up looking like this:-

ID      NAME        SEX
0001    John Doe    M
0002    Jane Doe    F
0003    Joe Bloggs  M

ID    FK    PRODUCT
0001  0001  Blue car
0002  0001  Yellow car
0003  0002  Pink bus
0004  0003  Black pen
0005  0003  Orange jacket

How would I go about doing this in SQL Server ?

Upvotes: 0

Views: 899

Answers (3)

Tomek
Tomek

Reputation: 3279

  1. Remove foreign key.
  2. Create customers2 table with extra column OldId and column Id as IDENTITY.
  3. Insert all rows from cutomers to customers2 (with mapping customers.Id to customers2.OldId)
  4. Do update on orders.fk setting orders.fk = customers2.id
  5. Do the same with the orders table what you did with the customers in point 2 and 3.
  6. drop customers, drop orders, rename customers2, rename orders2
  7. Recreate foreign key.

Upvotes: 0

marc_s
marc_s

Reputation: 754598

Absolutely no need to resort to cursor (yikes!) here.... you need something like this:

-- Table "Customers" - rename column "id" to "old_id"
EXEC sp_rename 'dbo.Customers.id', 'old_id'

-- add new "id" column
ALTER TABLE Customers ADD id INT 

-- fill new "id" column with sequential values, ordered by the "old_id" value
;WITH CTE AS
(
    SELECT old_id, new_id = ROW_NUMBER() OVER (ORDER BY old_id) 
    FROM Customers
)
UPDATE dbo.Customers
SET id = CTE.new_id
FROM CTE
WHERE CTE.old_id = dbo.Customers.old_id

-- Table "Orders" - rename column "id" to "old_id"
EXEC sp_rename 'dbo.orders.id', 'old_id'

-- add new "id" column
ALTER TABLE Orders ADD id INT 

-- update the FK references to the new "id" values in table "dbo.Customers"
UPDATE dbo.Orders 
SET fk = c.id
FROM dbo.Customers c
WHERE dbo.ORders.fk = c.old_id

-- fill new "id" column with sequential values, ordered by the "old_id" value
;WITH CTE AS
(
    SELECT old_id, new_id = ROW_NUMBER() OVER (ORDER BY old_id) 
    FROM dbo.Orders
)
UPDATE dbo.Orders
SET id = CTE.new_id
FROM CTE
WHERE CTE.old_id = dbo.Orders.old_id

-- drop the old, no longer required columns "old_id" from both tables
ALTER TABLE dbo.Customers DROP COLUMN old_id
ALTER TABLE dbo.Orders DROP COLUMN old_id

That'll work, if you don't have any other FK relationships that are referencing one of those two tables. If you do, then you might need to disable or drop those FK relationships before you start this upgrade script.

Upvotes: 6

E.J. Brennan
E.J. Brennan

Reputation: 46859

If this is a one time maintenace type fix you want to do, I would do this by:

  1. disable all the foregin keys
  2. add an oldid column to your customers table
  3. put the current ID field into the oldid field
  4. replace all the customer id's with new id's
  5. update the orders table with the now udpated id using a join/update on the oldid field from the customer table
  6. drop the oldid column
  7. add back your foriegn key constraints.

voila

Upvotes: 2

Related Questions