Posted by Tradeorganizer on 01/31/07 09:59

On Jan 31, 11:42 am, "Plamen Ratchev" <Pla...@SQLStudio.com> wrote:
> I am still confused about the logic and the purpose of this, but since it
> seems to follow the same pattern (when equal then 2 else 1), here it is (you
> just keep repeating the same for the other "nameval" columns):
>
> update test
> set nameval = (case when nametype1 = nametype2 then 2 else 1 end),
> nameval1 = (case when nametype1 = nametype3 then 2 else 1 end),
> nameval2 = (case when nametype1 = nametype4 then 2 else 1 end)
>
> Also, you can create those "nameval" columns as computed columns and then
> you do not have to run the update statements. Something like this:
>
> create table test(
> name varchar (20),
> address varchar (20),
> position varchar (20),
> nametype1 varchar (20),
> nametype2 varchar (20),
> nametype3 varchar(20),
> nametype4 varchar(20),
> nameval as (case when nametype1 = nametype2 then 2 else 1 end),
> nameval1 as (case when nametype1 = nametype3 then 2 else 1 end),
> nameval2 as (case when nametype1 = nametype4 then 2 else 1 end))
>
> insert into test (nametype1, nametype2, nametype3, nametype4) values ('AA',
> 'AA', 'AB', 'BA')
>
> select * from test
>
> drop table test
>
> Perhaps the table should be normalized too, but since no requirements are
> given I do not want to guess...
>
> HTH,
>
> Plamen Ratchevhttp://www.SQLStudio.com


is it possible to run the query in existing table , please suggest how
and also if there more than 20 nametypes is there query which can loop
through all the name types and do the job.

please suggest any reference too if any.

Regards

[Back to original message]