1

(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.

Community
  • 1
  • 1
billpg
  • 3,195
  • 3
  • 30
  • 57
  • Can the `UPDATE` be considered to know about what value `n` has in your constraint, or do would you like the code to be independent from the constraint (which is going to be difficult, if feasible at all)? – Romain Nov 25 '11 at 14:21
  • Seems fair enough that @MaxMyNumber would be visable to the UPDATE command. I'll leave the question of how to find the constraint limit (perhaps by digging into a sys table) for another day. – billpg Nov 25 '11 at 14:27
  • "I'm also hoping there won't have to be a table with the numbers 1 to n as contents" -- why do you say this? Such a table is useful for avoiding procedural code in SQL and/or making code portable (I assume that's why you are interested in using Standard SQL). – onedaywhen Nov 25 '11 at 14:30
  • @onedaywhen n could be thousands or millions, which would need a big table. I'm not against it in principle, I'm just hoping there's a way to avoid that. – billpg Nov 25 '11 at 14:32
  • What is this being used for, that the column didn't _start_ with an auto-increment value? Or at least (supposing SQLServer supports them) a sequence? That constraint combination is annoying for anyone attempting to insert to the table... Also, if these are being used for (potentially eventual) key/fk references, you wouldn't blink about having millions of rows with unique numbers (although I agree that just having a table for that many would be... uncomfortable, I guess, although probably useful - you could at least lock rows that way). – Clockwork-Muse Nov 25 '11 at 16:13
  • @X-Zero The MyNumber field was proposed as super-check-constraint method in a separate question. (See the question linked as "This is a followup" at the top of the question.) – billpg Nov 25 '11 at 16:21
  • @X-Zero Also, I don't mind having a table on disk that contains nothing but one to a million in integers. It just seems an awful waste! – billpg Nov 25 '11 at 17:36

3 Answers3

0

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.

shawty
  • 5,729
  • 2
  • 37
  • 71
  • `ROW_NUMBER()` is a fairly standard OLAP function, although not all of them have it. Although if you're going the route you seem to be (effectively, counting number of rows), there are similar ways around that... – Clockwork-Muse Nov 25 '11 at 16:06
0
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.

Branko Dimitrijevic
  • 50,809
  • 10
  • 93
  • 167
0

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.

Hogan
  • 69,564
  • 10
  • 76
  • 117