I have inherited a company database which issues an order number as a GUID. Not exactly user friendly when you want to quote your order number! So what I want to do is add a new int column and add a unique sequential order number to each existing order (which has been ordered by datetime).
It's a great idea, but I'm stuck on how to actually do it as an update query! Any help would be greatly appreciated.
Add an identity column to your table and the numbering will be taken care of for you.
alter table YourTable add YourTableID int identity
One way.
alter table t add order_id int identity
This will add an auto-incrementing int identity column. There is no guarantee as to the order in which the ids will be assigned to the existing rows, but a unique id will be assigned to each existing row. Each new row inserted after this change will get a new unique id.
Before applying this to a real application consider whether existing code will work with an identity column. Often this approach is a really harmless upgrade. Code that tries to insert an identity column fails, unless it uses set identity_insert. You can't remove the identity property without dropping the column.
To round this out might want a unique constraint on the new id, both for retrieval speed and to enforce uniqueness if the id column is ever updated.
Unfortunately if you just add an IDENTITY column to the table, the existing orders will not necessarily get the IDENTITY values assigned in order of the OrderDate, so they will be "out of order" if you wanted to assign order ID values based on order date (which seems logical). Quick example:
CREATE TABLE dbo.Orders
(
OrderGUID UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,
OrderDate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
INSERT dbo.Orders(OrderDate) VALUES
('20120101'), ('20120102'), ('20120103');
GO
ALTER TABLE dbo.Orders ADD OrderID INT IDENTITY(1,1);
GO
SELECT OrderID, OrderGUID, OrderDate = CONVERT(DATE, OrderDate)
FROM dbo.Orders
ORDER BY OrderDate;
Results (obviously yours will differ):
OrderID OrderGUID OrderDate
------- ------------------------------------ ----------
2 C5CE909E-0469-45AE-A828-647C7F54AA14 2012-01-01
1 70D8EEB1-FDA8-4E56-874F-771999C6DB84 2012-01-02
3 8E7B42C3-6C4D-4860-8A82-AFADDBA96A4A 2012-01-03
If this is not acceptable you should probably create a new table and insert all the old orders into it (at which point you can also drop the GUID column as I alluded to in my comment).
CREATE TABLE dbo.OrdersCopy
(
OrderID INT IDENTITY(1,1) PRIMARY KEY,
... other columns ...
);
INSERT dbo.OrdersCopy (OrderDate, ... other columns ...)
SELECT OrderDate, ... other columns ...
FROM dbo.Orders
ORDER BY OrderDate
OPTION (MAXDOP 1); -- single-threaded is important!
EXEC sp_rename 'dbo.Orders', 'OrdersOld', 'OBJECT';
EXEC sp_rename 'dbo.OrdersCopy', 'Orders', 'OBJECT';
(If you want to keep the old GUID for reference, temporarily, while you clean up other tables, that's probably fine, but you shouldn't make it auto-populate anymore, and you should plan to remove it since it's wide and redundant.)