June 17, 2012 at 7:25 am
Ok I have upgraded my works database from a poorly designed Access database to a SQL database. The previous system allowed NULL values and duplicates to be inserted into a field that should NOT ALLOW NULL Values or duplicates. Therefore, this issue has now been moved across to my new system as I cannot set these constraints on the field that has multiple NULL values.
My solution would be to use a sequential operator, so whatever = NULL would be changed to a sequential number that us as administrators would know was a bogus number starting at something like = 999999900 counting up from that. There are only 250 records that would require updating.
To make things more interesting this field is not a integer type, its a Varchar type as its a Hardware ID. Both numerical and characters are require.
I cannot get my code to work
UPDATE tblAsset SET HardwareNumber = r.NextID FROM tblAsset cross join (select 10000000 - ROW NUMBER() over (order by RAND()) AS NextID FROM tblAsset WHERE HardwareNumber is NULL) r WHERE tbl.Asset.HardwareNumber is NULL - This code only changes all NULL values in the HardwareID field to 99999999.
Please HELP.
June 17, 2012 at 9:36 am
You're encountering one of the "little" mishaps known with msaccess db.
did you try something like this ?
Declare @mycounter int = 999999900
UPDATE tblAsset
SET HardwareNumber = @mycounter
, @mycounter += 1
FROM tblAsset
WHERE HardwareNumber is NULL
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 17, 2012 at 10:20 am
Lucasprss (6/17/2012)
My solution would be to use a sequential operator, so whatever = NULL would be changed to a sequential number that us as administrators would know was a bogus number starting at something like = 999999900 counting up from that. There are only 250 records that would require updating.
Much better solution, since you're on SQL 2008. Create a unique filtered index on that column and filter it so that it does not contain nulls.
CREATE UNIQUE NONCLUSTERED INDEX idx_tblAsset_HardwareNumber on tblAsset (HardwareNumber)
WHERE HardwareNumber IS NOT NULL
No sequence columns necessary.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 17, 2012 at 10:21 am
Hi Lucas,
I chose your question as a learning exercise for myself. This is what work for me, I hope more experienced members on this forum don't kill me for this 🙂
DECLARE @@counter int
SET @@counter = 999999900
DECLARE UpdateRecords CURSOR FOR SELECT HardwareNumber FROM tblAsset WHERE HardwareNumber IS NULL
OPEN UpdateRecords
FETCH NEXT FROM UpdateRecords;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @@counter = @@counter + 1
UPDATE tblAsset SET HardwareNumber = @@counter
WHERE CURRENT OF UpdateRecords;
FETCH NEXT FROM UpdateRecords;
END
CLOSE UpdateRecords
DEALLOCATE UpdateRecords
GO
June 17, 2012 at 10:22 am
Thanks for your suggestion, I am getting a conversion error as my field is NVarchar and not a INT type. Any suggestions?
June 17, 2012 at 10:26 am
Lucasprss (6/17/2012)
Thanks for your suggestion, I am getting a conversion error as my field is NVarchar and not a INT type. Any suggestions?
Yes, look at my earlier post. No updates necessary.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 17, 2012 at 10:40 am
Thanks for your help, I will try it tonight and get back to you on how I get on.
June 17, 2012 at 12:11 pm
Tried the code above and got incorrect syntax near the keyword OF?
June 17, 2012 at 12:23 pm
Lucasprss (6/17/2012)
Tried the code above and got incorrect syntax near the keyword OF?
Please, have a look at the unique index that I suggested. That way you can get your machine numbers unique with multiple nulls with no fake columns, no expensive updates necessary.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 17, 2012 at 12:49 pm
If I create a Unique Index this surely will only display All values with Nulls or Or records with No Nulls. Basically I want to place a constraint on this field to stop Null Values and also No Duplicates Hardware ID's.
Do you still suggest a unique Index. I'd rather restrict the field by using a constraint.
June 17, 2012 at 1:22 pm
Why do you rather want a meaningless number over a null? If you're going to be fixing the data up surely a null is easier to find than a meaningless value for fixing.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 17, 2012 at 1:26 pm
This code did execute I made a typo, but it doesnt work. Executes but made no changes ;( Any Suggestions
June 17, 2012 at 11:55 pm
I like the solution Gail handed over.
Especially because it lets you identify the bogus information easily using "WHERE HardwareNumber is NULL".
So, unless you really need this column to be unique as a primary key function, this solution will make your data system work again and provide you some time to fix the rows containing the unwanted nulls.
After these are fixed, you can alter the column to be "not null" and create a regular unique index and remove the filtered index.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply