Null values to = unique ID to allow No duplicates

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Null values to = unique ID to allow No duplicates

Author

Topic

rustynails28
Starting Member

12 Posts

Posted - 2012-06-17 : 09:59:55

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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-17 : 10:55:14

so you want NULL values to be

Posted - 06/17/2012 : 09:59:55

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 r
SET HardwareNumber = r.NextID FROM (select 10000000 - ROW NUMBER() over (order by HardwareNumber) AS NextID FROM tblAsset WHERE HardwareNumber is NULL) r

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rustynails28
Starting Member

12 Posts

Posted - 2012-06-17 : 11:23:49

I want the NULL values to be a unique number with an increment of 1. Starting at 999999900

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-17 : 11:31:26

thsn shouldnt it be?


UPDATE r
SET HardwareNumber = r.NextID FROM (select  999999899 + ROW NUMBER() over (order by HardwareNumber) AS NextID FROM tblAsset WHERE HardwareNumber is NULL) r

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rustynails28
Starting Member

12 Posts

Posted - 2012-06-17 : 11:51:12

What do you suggest? Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-17 : 11:51:43

first try and see if that gives you intended result...

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rustynails28
Starting Member

12 Posts

Posted - 2012-06-17 : 14:24:51

No luck. I have tried the following but I am getting the error Incorrect syntax near the keyword OF? Any suggestions.

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-17 : 15:02:52

this is not even near to what i suggested.
Why are you using cursor? you just needed the suggestion i gave. it does id assignment in a batch

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rustynails28
Starting Member

12 Posts

Posted - 2012-06-17 : 15:38:29

Hi, very sorry only just seen the amended version. I tried it and have got error Msg 102, Level 15, State 1, Line 6. Incorrect syntax near r?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-17 : 15:49:58

are you sure you were using below suggestion itself?


UPDATE r
SET HardwareNumber = r.NextID 
FROM (select  999999899 + ROW NUMBER() over (order by HardwareNumber) AS NextID,HardwareNumber FROM tblAsset WHERE HardwareNumber is NULL) r

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rustynails28
Starting Member

12 Posts

Posted - 2012-06-17 : 16:00:22

Im now getting msg 102, level 15, state 1, line 3 incorrect syntax near '('

rustynails28
Starting Member

12 Posts

Posted - 2012-06-17 : 16:03:26

Well done, I just changed a parameter and it works. Well done thanks for your help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-17 : 16:12:06

welcome

in future please try to use suggestion as given to avoid confusion!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources