SQL Server Performance Forum – Threads Archive

help required

Hi I have 2 tables as below Table 1
—————
CZone Cheerzone
MAURITIUS MOBILE NULL
TANZANIA MOBILE NULL
BANGLADESH MOBILE NULL
BANGLADESH MOBILE – GRAMEEN NULL
GHANA MOBILE NULL
NIGER MOBILE NULL
NIGER MOBILE – CELTEL NULL
NIGERIA MOBILE – ECONET NULL
NIGERIA MOBILE – GLOBACOM NULL
NIGERIA MOBILE – MTEL NULL Table 2 ( with just 1 column as below)
————- Name
MAURITIUS MOBILE
TANZANIA MOBILE
BANGLADESH MOBILE
GHANA – ZZ MOBILE
NIGER – ZZ MOBILE
NIGER – ZZ MOBILE
NIGER – ZZ MOBILE
NIGER – ZZ MOBILE
NIGER – ZZ MOBILE
I need to update CheerZone in table 1 with name in table 2 matching the table1.czone and table2.name
If it does not match for example
GHANA MOBILE (in table A ) does not have a match in table 2. In such case i update the cheerzone in table1 with GHANA – ZZ MOBILE and likewise for other zones.
How can i achieve it? ( Hope i made myself clear ) Thanks

It’s not entirely clear wehat you want to insert in case there is no match – perhaps the country name + ‘ – ZZ MOBILE’? In that case your table is less than ideal in design, since you cannot reliably read the country name from the available data. You may say that the country name is what you see before the first blank space, like here: Mauritius, Tanzania, Bangladesh, etc. But what about countries with multi-part names, like Sierra Leone, Ivory Coast, Dem. Rep. of Congo … Okay, assuming Table1 also has a column with the country name, which you haven’t told us about, your update query would be like this … UPDATE Table1 SET CheerZone =
CASE WHEN Table2.Name IS NULL
THEN Table1.Country + ‘ – ZZ MOBILE’
ELSE Table2.Name END FROM Table1
LEFT JOIN Table2
ON Table1.CZone = Table2.Name So there are three techniques used here:
(1) To use a FROM clause in an UPDATE query to combine information from different tables – only one of these tables can be mentioned in the UPDATE clause, and only columns from that table can be updated.
(2) To use a LEFT join to see all records from Table1, and matching records from Table2, and NULLs on the column from Table2 where there is no match.
(3) To use a CASE statement to verify if there is a matching record from Table2, and if not then to insert a dummy value.

Thanks Adriaan. My scenario is like this. Our carrier partners charge us based a list of zones (countries) defined by them. And my company charge our customer based a list of zones (countries) defined by us.
Table 1 – zones defined by our carrier partners
Tbale 2 – zones defined by company What im trying to achieve is to make a match of both zones and in case the zones does not match, then i update with the table 2 zones, as i mentioned earlier GHANA MOBILE is in table 1 but not in table 2. So i update the cheerzone coulmn in table 1 with the "best fit" from table 2 which will be GHANA – ZZ MOBILE I guess my requirment soinds really weird with the kinda table design that i have, but this is what i was trying to achieve with the given info to me. Thanks
Avinash

These are the columns i have in the table Table 1
CZone,Price,dd,mm,yyyy,Cheerzone
MAURITIUS,0.0656,8,2,2006,
MAURITIUS MOBILE,0.0656,8,2,2006,
TANZANIA MOBILE,0.0970,28,2,2006,
BANGLADESH MOBILE,0.03424554,9,3,2006, Tabe 2 NAME,ZONEID
Macau ,116
Macedonia ,117
Madagascar ,118
Inmarsat MOBIQ – Pacific Ocean ,728
Malawi ,120
Malaysia ,121
Maldives ,122
Mali ,123
Malta ,124
Marshall Islands ,125 I dont hav a clumn name like COUNTRY in table 1

How many records are you talking about? I guess you really could do with a country column, plus a reference table to make sure people don’t start entering different country names. There is an ISO list of country codes, which is used in all lines of business as a standard.

Yes thats what im going to do now Adriaan. We have like 1100 records maximum. I will create a master table with a country coulmn as suggested by you.
I dont think there is any other way of acheving it i guess. Many Thanks
Avinash

]]>