(This is a follow-up to the answer by "onedaywhen" on a question I posted earlier today.)
Hi everyone. Say I have a table MyTable with two int fields, PrimaryKey and MyNumber. (And other fields not directly relevant to this question). MyNumber has a unique constraint and a check constraint limiting it to BETWEEN 1 AND n. (Let's say n=5 for now.)
1,2
2,NULL
3,5
4,NULL
5,NULL
6,1
7,NULL
How could an UPDATE be written to change the record where PrimaryKey=2 so that MyNumber has a non-NULL value? I don't care what value it has, so long as it's not a NULL and meets it's two constraints of being unique and within range.
I'm using MS SQL Server, but I'm hoping there's an answer using standard SQL.
(I'm also hoping there won't have to be a table with the numbers 1 to n as contents.)
Many thanks.
WITH CTE AS (
SELECT 1 N
UNION ALL
SELECT N + 1 FROM CTE WHERE N < 5
)
UPDATE MyTable
SET MyNumber = (
SELECT TOP 1 N FROM CTE
WHERE NOT EXISTS (SELECT * FROM MyTable WHERE MyNumber = N)
)
WHERE PrimaryKey = 2
In plain English:
- Generate integers between 1 and 5 (
WITH CTE AS ...).
- Pick the first of these integers that does not already exist in MyNumber (
SELECT TOP 1 ...).
- Assign that integer to MyNumber, in the row identified by PrimaryKey = 2 (
UPDATE ...).
If this query fails to find a suitable value, it will simply set the MyNumber to NULL.
WARNING: This might still viloate UNIQUE constraint on MyNumber in a concurrent environment (i.e. when two concurrently-executing transactions try to run this same query in parallel). So, you'd have to be prepared to retry the query if necessary.
Look up the @ROW_NUMBER function for MS-SQL, I can't off the top of my head think of code right now, and I'm not near my DB server to do some tests, but if memory serves, @ROW_NUMBER in association with a 'IS NOT NULL' check should help you achive your goal.
@ROW_NUMBER documentation : http://msdn.microsoft.com/en-us/library/ms186734.aspx
For other flavours of SQL i can't think of a solution at present.
I can't help but point out that the next version of SQL (code name Denali) will support SEQUENCES, clearly ideal in this case.
The code would look something like this:
CREATE SEQUENCE Count1to5
START WITH 1
INCREMENT BY 1
MAX VALUE 5;
I'm not sure exactly how or if you would be able to skip values on updates using a where and I don't have a test server set up with the beta, but it seems like it is worth mentioning this solution will be available soon.
