How do io autopopulate a field with a certain valu

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How do io autopopulate a field with a certain valu

Author

Topic

prakash35
Starting Member

1 Post

Posted - 2009-02-27 : 18:57:17

Hi,

I need a field populated with a country name based on the account number that i key in.

For e.g, All accounts that start with 1234 will update the form as Singapore, all accounts with 4567 will the be updated as Australia.

Really need some help on this. Thanks.

Prakash

mfemenel
Professor Frink

1421 Posts

Posted - 2009-02-27 : 20:00:22

You'd just do an update statement with a case. See example:

--drop table #changeme
create table #changeme(countrycode varchar(100))

insert into #changeme values('Cuba')
GO
select * from #changeme

--Try different values for testing
declare @testing int
set @testing=1

update #changeme
set countrycode=case @testing when 456 then 'USA'
when 123 then 'CUBA'
when 567 then 'Germany'
else 'UNKNOWN' END
select * from #changeme

Mike
"oh, that monkey is going to pay"

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2009-02-28 : 11:25:03

You could do it as 2 statements , the first looks up the country name based on the code , stores it in a variable , and then do the update statement , using the stored value

Jack Vamvas
--------------------
http://www.ITjobfeed.com

GhantaBro
Posting Yak Master

215 Posts

Posted - 2009-02-28 : 23:54:39

Create a trigger for insert or use a lookup table and run update query as per the requirement.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-02 : 09:22:32

quote:
Originally posted by jackv

You could do it as 2 statements , the first looks up the country name based on the code , stores it in a variable , and then do the update statement , using the stored value

Jack Vamvas
--------------------
http://www.ITjobfeed.com

why do it one by one. whats the problem with batch update statement based on lookup code?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-02 : 09:26:08

another way is to use a scalar valued udf which returns the country code for each of account value passed and link it via default constraint to reqd column. something like


CREATE TABLE tablename
(AccntCode int,
....,
Country varchar(100) DEFAULT dbo.GetCountry(AccntCode)
)

but this might not perform as better as batch update as udf will be executed for each row value.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources