patch null value?

following is my sql:

select a.dcode, b.district from table1 a, table2 b
where
a.id = b.id

and return following result:

dcode district
123 south
321 north
456 east
789 west
123
789

so for those records that district are null, how can i fill them up as i know they have the same dcode as the other records?

use Isnull or coalesce

Code Snippet

select a.dcode, isnull(b.district,-1) from table1 a, table2 b

where

a.id = b.id

--or

select a.dcode, COALESCE(b.district,-1) from table1 a, table2 b

where

a.id = b.id

|||

This could possibly point towards a de-normalized database design.

It might be better if you created a separate lookup table to store a distinct list of 'dcode' values against the appropriate 'district' value (and possibly even split distinct 'district' values out to a second lookup table) - you could then store 'dcode' in both table1 and table2 and remove the 'district' column from table2. Depending on your database design there may even be scope to store 'dcode' in either table1 or table2 (and not both).

Granted, you might have to re-work substantial amounts of code but it would help you in situations such as the one you're currently facing and would, more importantly, help to maintain the integrity of your data.

Chris

|||

Joe,

Below is one way that you could accomplish this.

declare @.temptable table
(dcode int,
district varchar(20))

insert into @.temptable values (123,'south')
insert into @.temptable values (321,'north')
insert into @.temptable values (456,'east')
insert into @.temptable values (789,'west')
insert into @.temptable values (123,NULL)
insert into @.temptable values (789,NULL)

select a.dcode,COALESCE(a.district,b.district,'')
from @.temptable a
left join (select dcode,district
from @.temptable
where district is not null) b
on a.dcode = b.dcode

Let me know if you have any other questions.

crusso.

|||

I completely agree with Chris that you have an improperly desiged database.

First thing: You need to create a District table that has a district code/Id and description, and use this code in both of your tables. This fixes the inconsistency of codes.

Second: Why are you recording district in both tables? (your names give no clue) Does this district mean the same thing? Or could the be different for some logical reason? If they have the same meaning, then delete one and join to get the other one if you need it in a query. This will make sure that you don't get out of sync values.

|||

i've figured myself how to solve this problem:

select a.dcode, b.district into #temp from table1 a, table2 b

where

a.id = b.id

I

I

I

V

update #temp
set district = b.district
from #temp a, (select * from #temp) b
where
a.dcode = b.dcode
and
a.district is null