| Author |
 Topic  |
|
|
rustynails28
Starting Member
United Kingdom
12 Posts |
 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 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
India
47173 Posts |
Posted - 06/17/2012 : 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
United Kingdom
12 Posts |
Posted - 06/17/2012 : 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
India
47173 Posts |
Posted - 06/17/2012 : 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
United Kingdom
12 Posts |
Posted - 06/17/2012 : 11:51:12
|
| What do you suggest? Thanks |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 06/17/2012 : 11:51:43
|
first try and see if that gives you intended result...
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
rustynails28
Starting Member
United Kingdom
12 Posts |
Posted - 06/17/2012 : 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
India
47173 Posts |
Posted - 06/17/2012 : 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
United Kingdom
12 Posts |
Posted - 06/17/2012 : 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
India
47173 Posts |
Posted - 06/17/2012 : 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
United Kingdom
12 Posts |
Posted - 06/17/2012 : 16:00:22
|
| Im now getting msg 102, level 15, state 1, line 3 incorrect syntax near '(' |
|
|
|
rustynails28
Starting Member
United Kingdom
12 Posts |
Posted - 06/17/2012 : 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
India
47173 Posts |
Posted - 06/17/2012 : 16:12:06
|
welcome 
in future please try to use suggestion as given to avoid confusion!
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
Null values to = unique ID to allow No duplicates
