Updating Column Where Value is Null with Previous Value

Question

Updating Column Where Value is Null with Previous Value

Jersey Moe

SSChasing Mays

Points: 652
More actions
October 6, 2010 at 1:52 pm

#387952

Looking for some suggestions on how to perform an update. Here is the task.
One table has two columns (Group_Name and User_ID). the User_ID is always populated but, the Group_Name is not. Data extract parametes can not be adjusted and no way to get at back end db either.
A user_id can appear in more then one group. but the group name is unique.. so its a many to one condition. Group_name would be the value to relate to other tables.
table data looks like this after the initial data load:
Group1.User1
NULL....User2
NULL....USER2
Group2.User1
NULL....User5
Group3.User6
NULL....User9
...
How to I update the table to look like this so that I can run a group by:
Group1.User1
Group1.User2
Group1.USER2
Group2.User1
Group2.User5
Group3.User6
Group3.User9
...
Appreciate any thoughts or ideas.

Viewing 12 posts - 1 through 11 (of 11 total)

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

Jeffrey Williams SSC Guru Points: 90244 More actions · Answer 1

First, you need to identify how a user is associated to a particular group. Looking at the data, there is no way to tell that User2 belongs to Group1. How do you determine what group the user belongs to?

If you say it is because of the order the rows are returned - then you need to provide the columns that determine that order. We cannot rely upon insertion order either, unless you have some column available that defines when that row was inserted.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Leju Geevarghese SSC Eights! Points: 982 More actions · Answer 2

Am I am guessing your question correctly, when ever a Null is encountered in the group field, you want to update the null to previous records group value which is not Null?

---------------------------------------------------------------------------------------
It begins by taking the first step.

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 3

Jeff is correct unless you dont have a unique value you will not be able update the prv column with your data columns.you dont have any unique field as id or datetime in your table so you cannt be able to update those cloumns. On what condition you will update how do u know when the data has been inserted in which order.Unless and until u dont have a unique column you will be able to update in this case. Study normalization,Codds rule this discribes more on this.how to create a table on which bases.Hope you could have got some idea what to do now

Thanks

Parthi

Thanks
Parthi

Jersey Moe SSChasing Mays Points: 652 More actions · Answer 4

Yes, I would like to update the 'Null' value to the previous non 'Null' value

Jersey Moe SSChasing Mays Points: 652 More actions · Answer 5

so if I understan, you are suggesting adding an identiklty column. This way I can address a row via a unique value. Yes?

Leju Geevarghese SSC Eights! Points: 982 More actions · Answer 6

Now Parthi's previous comments comes into play, may be you have another field in the table that identifies how the rows were inserted. List the entire table definition

---------------------------------------------------------------------------------------
It begins by taking the first step.

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 7

You add an identity column to hold ordering in the same positions they're already stuck in. Then you modify a few things like maxdop to make sure you don't shoot your ordering in the foot.

Then you would do a 'running totals' trick to get the last value to the next row. See this article for some details:

http://www.sqlservercentral.com/articles/T-SQL/68467/

What it basically does is carry the previous entry to the next line, allowing you to transfer it over. So you'll end up with a statement (this is psuedocode) like this:

DECLARE @carryme VARCHAR(1000)

UPDATE tbl

SET

@carryme = NullableCol = @carryme

FROM

tbl

ORDER BY

tblID

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Leju Geevarghese SSC Eights! Points: 982 More actions · Answer 8

You could do that and verify if the rows are listed in order you had inserted.

Then open a cursor, loop through the records ordered by this identity field and update the group field records.

---------------------------------------------------------------------------------------
It begins by taking the first step.

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 9

Sorry, bad code, it should have been:

UPDATE tbl

SET

@carryme = NullableCol = ISNULL( NullableCol, @carryme)

FROM

tbl

ORDER BY

tblID

Oops.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Jersey Moe SSChasing Mays Points: 652 More actions · Answer 10

cool trick, I'll give it try. Thanks for the replies>

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 11

Jersey Moe (10/6/2010)
cool trick, I'll give it try. Thanks for the replies>

It may be a cool trick but you MUST not use it in this case. Unless you simply didn't post it, you have NOTHING in the data that will guarantee the order of the rows which is absolutely essential to doing the job correctly. Somehow you have to load the data with at least an IDENTITY or other incrementing column to guarantee the order. That's true even for a cursor never mind the Quirky Update.

I say again... unless you have something in the table that is both unique and can identify the correct order of the rows, you're pretty much stuck. Don't give the "Quirky Update" a bad name by using it inappropiately. Thanks.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)