Update Table by Replacing NULLS

Question

Post reply

Update Table by Replacing NULLS

surya-236315

SSC Eights!

Points: 850
More actions
October 13, 2005 at 10:04 am

#91426

Hi all,

I am trying to create a table like this below. I need to replace the NULL values in the COL3 with the max. no. of col3 till that row. means I need to replace the NULLS where col1 = D, col1 = E; with 3. and the NULLs where COL1 = H,I should be replaced with 5.

This is the table I created.

COL1             COL2             COL3
A                     1                      1
B                      1                      2
C                     1                      3
D                     0                      NULL
E                      0                      NULL
F                      1                      4
G                     1                      5
H                     0                      NULL
I                       0                      NULL
My Table supposed to look like this
COL1             COL2             COL3
A                     1                      1
B                      1                      2
C                     1                      3
D                     0                      3
E                      0                      3
F                      1                      4
G                     1                      5
H                     0                      5
I                       0                      5
Any help will be appreciated.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Farrell Keough SSCoach Points: 17414 More actions · Answer 1

DECLARE @OriginalTable TABLE( COL1 varchar(5),

COL2 integer,

COL3 integer)

INSERT INTO @OriginalTable

SELECT 'A', 1, 1 UNION ALL

SELECT 'B', 1, 2 UNION ALL

SELECT 'C', 1, 3 UNION ALL

SELECT 'D', 0, NULL UNION ALL

SELECT 'E', 0, NULL UNION ALL

SELECT 'F', 1, 4 UNION ALL

SELECT 'G', 1, 5 UNION ALL

SELECT 'H', 0, NULL UNION ALL

SELECT 'I', 0, NULL

UPDATE @OriginalTable SET

COL3 = (SELECT MAX( OT.COL3) FROM @OriginalTable OT

WHERE OT.COL3 IS NOT NULL

AND ASCII( OT.COL1) < ASCII( O.COL1))

FROM @OriginalTable O

WHERE O.COL3 IS NULL

SELECT * FROM @OriginalTable

I wasn't born stupid - I had to study.

surya-236315 SSC Eights! Points: 850 More actions · Answer 2

surya-236315

SSC Eights!

Points: 850

October 13, 2005 at 11:00 am

#597169

Thanks a lot Farell, worked me great!

Farrell Keough SSCoach Points: 17414 More actions · Answer 3

Glad to help.

I wasn't born stupid - I had to study.

Farrell Keough SSCoach Points: 17414 More actions · Answer 4

You lost me Joe. Your psuedo-code has no equal and has a missing paren.

"proprietary UPDATE... FROM.. syntax."? Can you explain for me?

Thanks

I wasn't born stupid - I had to study.

Farrell Keough SSCoach Points: 17414 More actions · Answer 5

That is why I used ASCII. It did not matter which of the previous records was used, only that the MAX( Col3) WHERE the ASCII( Col1) was less than the value UPDATED.

I can see the point of the ORDER BY if the multiple values 'could' be chosen, but in this case, MAX fulfilled the requirment...

Thanks for the explanation, though. That is important to keep in mind.

I wasn't born stupid - I had to study.