update a table using another table

Posted by Klbaiju under Sql Server on 1/29/2010 | Views : 1081 | Status : [Member] | Replies : 3
Hi

i have a table mobile

it contains


mobileno cname

9350667 arun
9345678 kiran
9256788 sarun
9448888 varun
987777 kumar

another table detail
it contains
name company

i want to update table detail that contains cname and company

if the mobile no start with 93 it should be reliance
if it 92 that is tata
if it 94 that is bsnl
if it 98 that is idea

the company table should be


the detail table should be like this

cname company
arun reliance
kiran reliance
sarun tata
varun bsnl
kumar idea


how it is possible.

thanks in advance

K L BAIJU




Responses

Posted by: Nishithraj on: 1/29/2010 [Member] Bronze

Up
0
Down
You can have a select case statement to check the phone no as shown

SELECT mobileno 

CASE
WHEN SUBSTRING(mobileno , 1, 2) = '92' THEN 'tata'
WHEN SUBSTRING(mobileno , 1, 2) = '94' THEN 'bsnl'
WHEN SUBSTRING(mobileno , 1, 2) = '98' THEN 'idea'

etc
END


also join the tables that you want to get the result set.

Mark this as answer, if it is.....

With regards
Nishithraj Narayanan

Klbaiju, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sagarp on: 1/29/2010 [Member] Bronze

Up
0
Down




Nishithraj code work u can use that code

Thanks
SagarP
http://www.emanonsolutions.net
http://emanonsolutions.blogspot.com/

Klbaiju, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: RtpHarry on: 1/29/2010 [Member] [MVP] Bronze

Up
0
Down
Well I was going to get into a long post about setting up a Linq2Sql data context, using a regular expression to extract the prefix code and then translate it into a collection of records to be reinserted into the database.

I think it would have been a bit over-engineered :)

Klbaiju, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response