Bit of a novice to this and, even worse, self-taught so could be missing something really obvious. Anyway, would very much appreciate help with:
Got a SQL table with a column that has NULLS in it. What I want to do is fill down the value in the row above based on a condition in another column (recordtype). Came up with the following code but unfortunately it only fills in the record directly below. Not those further down (variable number of NULL rows). Any idea how to get these to fill down? Suspect the answer may lie with cursors but 1) I'm told they're slow (I have 2 and 1/2 million records in this table) and 2) I've never used cursors before!
Ideas anyone?
_______________ _
Update dbo.NSTS
Set dbo.NSTS.Number = NSTS_1.Number
FROM dbo.NSTS LEFT OUTER JOIN
dbo.NSTS AS NSTS_1 ON dbo.NSTS.[Id] = ((NSTS_1.[Id])+1)
WHERE dbo.NSTS.Record Type like '101' AND (dbo.NSTS.Numbe r is null or dbo.NSTS.Number like '')
_______________ _
Example of data table here:
ID RecordType Number
1 100 4325273182
2 101 NULL
3 101 NULL
4 101 NULL
5 100 4342684020
6 100 4342696800
7 100 4304028782
8 100 4305914743
9 101 NULL
10 101 NULL
11 100 4328179942
12 101 NULL
13 101 NULL
14 101 NULL
15 101 NULL
16 100 4329313804
17 100 4329874164
18 101 NULL
19 100 4330053764
20 101 NULL
Got a SQL table with a column that has NULLS in it. What I want to do is fill down the value in the row above based on a condition in another column (recordtype). Came up with the following code but unfortunately it only fills in the record directly below. Not those further down (variable number of NULL rows). Any idea how to get these to fill down? Suspect the answer may lie with cursors but 1) I'm told they're slow (I have 2 and 1/2 million records in this table) and 2) I've never used cursors before!
Ideas anyone?
_______________ _
Update dbo.NSTS
Set dbo.NSTS.Number = NSTS_1.Number
FROM dbo.NSTS LEFT OUTER JOIN
dbo.NSTS AS NSTS_1 ON dbo.NSTS.[Id] = ((NSTS_1.[Id])+1)
WHERE dbo.NSTS.Record Type like '101' AND (dbo.NSTS.Numbe r is null or dbo.NSTS.Number like '')
_______________ _
Example of data table here:
ID RecordType Number
1 100 4325273182
2 101 NULL
3 101 NULL
4 101 NULL
5 100 4342684020
6 100 4342696800
7 100 4304028782
8 100 4305914743
9 101 NULL
10 101 NULL
11 100 4328179942
12 101 NULL
13 101 NULL
14 101 NULL
15 101 NULL
16 100 4329313804
17 100 4329874164
18 101 NULL
19 100 4330053764
20 101 NULL