Re: Filling null values

On 08/05/2011 07:32 PM, jeffrey wrote:

I have a table that looks like this:

homeid    city          date     measurement      pre/post
123   san francisco  1/2/2003     1458             pre
123   san francisco  NULL          1932             post
124   los angeles    2/4/2005      938               pre
124   NULL            NULL           266               pre
124   los angeles    7/4/2006      777               post

I'd like to write a query so that I get the following result:

homeid    city          date     measurement      pre/post
123   san francisco  1/2/2003     1458             pre
123   san francisco  1/2/2003      1932            post
124   los angeles    2/4/2005      938               pre
124   los angeles    2/4/2005       266              pre
124   los angeles    7/4/2006      777               post

If a city or date is null, then it will fill from other not null
values with the same homeid.  If given the choice, it will
preferentially fill from a row where homeid AND pre/post match.  But
if that doesn't match, then it will still fill from the same homeid.

Does anyone have ideas for this?

Thanks,
Jeff

You need a primary key on the table to do this. Then you can do a self join and update the correct values.

Sim