You can accomplish this using ROW_NUMBER()
syntax, as mentioned by @Kin :
USE Tempdb;
CREATE TABLE dbo.testRowNum
(
ID INT NOT NULL PRIMARY KEY CLUSTERED IDENTITY(1,1)
, Name VARCHAR(255)
, SequenceNum INT NULL
);
INSERT INTO testRowNum (Name) VALUES ('A'), ('B'), ('C');
SELECT *
FROM dbo.testRowNum;
UPDATE dbo.testRowNum
SET SequenceNum = x.RowNum
FROM dbo.testRowNum
INNER JOIN (SELECT ID, ROW_NUMBER() OVER (ORDER BY ID) AS RowNum FROM dbo.testRowNum) x ON testRowNum.ID = x.ID
WHERE SequenceNum IS NULL;
SELECT *
FROM dbo.testRowNum;
Output:
If you want only update certain rows, you can use a Table Variable (or a #temp table if you are updating a LOT of rows), as such:
DECLARE @IDsToUpdate TABLE
(
ID INT
);
INSERT INTO @IDsToUpdate VALUES (1),(3);
UPDATE dbo.testRowNum
SET SequenceNum = x.RowNum
FROM dbo.testRowNum
INNER JOIN (
SELECT t.ID
, ROW_NUMBER() OVER (ORDER BY t.ID) AS RowNum
FROM dbo.testRowNum t
INNER JOIN @IDsToUpdate i ON t.ID = i.ID
) x ON testRowNum.ID = x.ID
WHERE SequenceNum IS NULL;
The example above results in the following output:
An improvement (based on a suggestion by Daniel Hutmacher) avoids the join and results in a more efficient query plan (no join or Halloween protection):
WITH Numbered AS
(
SELECT
SequenceNum,
RowNum =
ROW_NUMBER() OVER (
ORDER BY ID)
FROM dbo.testRowNum
)
UPDATE Numbered
SET SequenceNum = RowNum;
Or, when using the @IDsToUpdate
table:
WITH Numbered AS
(
SELECT
SequenceNum,
RowNum =
ROW_NUMBER() OVER (
ORDER BY ID)
FROM dbo.testRowNum
WHERE EXISTS
(
SELECT *
FROM @IDsToUpdate AS I
WHERE I.ID = dbo.testRowNum.ID
)
)
UPDATE Numbered
SET SequenceNum = RowNum;
Let's say the new default you want is 5
:
DECLARE @sql NVARCHAR(MAX) = N'';
SELECT @sql += N'ALTER TABLE ' + t + ' DROP CONSTRAINT ' + c + ';
ALTER TABLE ' + t + ' ADD CONSTRAINT ' + c
+ ' DEFAULT (5) FOR [project_id];'
FROM
(
SELECT t = QUOTENAME(s.name) + '.' + QUOTENAME(t.name),
c = QUOTENAME(d.name)
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
INNER JOIN sys.columns AS c
ON t.[object_id] = c.[object_id]
INNER JOIN sys.default_constraints AS d
ON d.parent_object_id = t.[object_id]
AND d.parent_column_id = c.column_id
WHERE c.name = N'project_id'
) AS x;
PRINT @sql;
--EXEC sp_executesql @sql;
Notes:
print
is limited to 8K, so if you have a lot of tables with this column, you might not actually see the entire command output in SSMS, and it may look truncated.
- This will only add the constraint to
project_id
columns that already have a default constraint. If you have other tables that have this column but don't currently have a constraint, you'll need a separate query.
- This will not change the values already generated based on the old defaults.
Here is why I recommend against INFORMATION_SCHEMA for this type of problem:
Best Answer
If you have to do it inside of a procedure with a set amount of
BetGenerationBetID
's, then you could use user defined table types.Step 1: Create Type and test table
Step 2: Create the Procedure that uses the table type
Step 3: Enter your values and run the procedure
Validate
Result