update a table using another table

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

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

0	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

0	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

Latest Posts