Hi,

I have a database with table name as test in that i have 6 colums
they are

name varchar (20)
address varchar (20)
position varchar (20)
nametype1 varchar (20)
nametype2 varchar (20)
nameval varchar(20)

now in the nametype1 and nametype2 there are values like
nametype1 nametype2
"AB" "BA"
"BB" "BB"
"AA" "AA"
"BA" "AB"

now depending upon the combination i want to assign value to the thrid
field that is nameval like example below

nametype1 nametype2 nameval
"AB" "BA" 1
"BB" "BB" 2
"AA" "AA" 2
"BA" "AB" 1

please suggest query in sql which i can run to do this .

Regards

Hi,

You do not specify what is the logic for generating the values for the
nameval column, but based on your example seems it is 2 when the values are
equal and 1 when they are different.

Based on that assumption, here is a query that will do the update:

update test
set nameval = (case when nametype1 = nametype2 then 2 else 1 end)

Regards,

Plamen Ratchev
http://www.SQLStudio.com

[quoted text, click to view]


hi i have an update to the query , please suggest if the table
structure and results are below then what should i run for no of
fileds

name varchar (20)
address varchar (20)
position varchar (20)
nametype1 varchar (20)
nametype2 varchar (20)
nametype3 varchar(20)
nametype4 varchar(20)
nameval varchar(20)
nameval1 varchar(20)
nameval2 varchar(20)
nameval3 varchar(20)

now in the nametype1 and nametype2 there are values like
nametype1 nametype2 nametype3 nametype4
"AB" "BA" "BB" "BB"
"AA" "AA" "BA" "AB"
"AB" "BA" "BB" "BB"
"AA" "AA" "BA" "AB"

now depending upon the combination i want to assign value to the thrid
field that is nameval like example below

nametype1 nametype2 nameval
"AB" "BA" 1
"AA" "AA" 2
"AB" "BA" 1
"AA" "AA" 2

nametype1 nametype3 nameval1
"AB" "BB" 1
"AA" "BA" 1
"AB" "BB" 1
"AA" "BA" 1

nametype1 nametype4 nameval2
"AB" "BB" 1
"AA" "AB" 1
"AB" "BB" 1
"AA" "AB" 1

please suggest query in sql which i can run to do this also i would
like to know is it possible to have some kind of loop which can check
each nametype with other like the combination above please suggest.

Regards

[quoted text, click to view]


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

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.

Even your narrative is wrong. Did you know that double quotes are not
used for string values in SQL? Have you heard of ISO-11179 rules for
data element names? Absurd things like "name_type_1" look like a
repeating group in violationof 1NF which will lead to some really
horrible kludges and a loss of data integrity.

Please try again and pretend that you have to work from these specs
without any prior knowledge -- we do SQL, not mind-reading :)

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 Ratchev
http://www.SQLStudio.com

Yes, the query will update an existing table, just keep adding lines for
each column, like this:

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),
nameval3 = (case when nametype1 = nametype5 then 2 else 1 end),
...

As for you reference to do the update in a loop, then this is doable via
dynamic SQL. Here is a quick sketch of how it may look (by adjusting the
number 10 you will get different number of columns, beware of the 4000
character limit on the SQL string):

DECLARE @sql nvarchar(4000),
@count int,
@numcolumns int

SELECT @sql = 'update test set ', @count = 1, @numcolumns = 10

WHILE @count <= @numcolumns
BEGIN
IF @count = 1
SELECT @sql = @sql + 'nameval = (case when nametype1 = nametype' +
CAST(@count + 1 as nvarchar) + ' then 2 else 1 end)'
ELSE
SELECT @sql = @sql + ', nameval' + CAST(@count - 1 as nvarchar) + ' =
(case when nametype1 = nametype' + CAST(@count + 1 as nvarchar) + ' then 2
else 1 end)'

SELECT @count = @count + 1
END

EXEC(@sql)

Erland Sommarskog has an excellent guide on dynamic SQL at
http://www.sommarskog.se/dynamic_sql.html. I would recommend reading it
before jumping into using dynamic SQL. Also, please read the comment from
Celko, he is correct that without posting DLL and specifications it is very
difficult to get good answers.

Regards,

Plamen Ratchev
http://www.SQLStudio.com

[quoted text, click to view]

A couple of things that others have touched on, but not made
quite this explicit:

/Why/ should nametype1 = "AB" and nametype2 = "BA" lead to
nameval = 1? What is the general rule that you want to apply?

Why do you have more than 20 nametypes? Please give serious
consideration to splitting this table into two tables, e.g.

[table1]
person_id, name, address, position
1, 'John Doe', '123 Cherry Lane', 'Regional Manager'
2, 'Thomas Atkins', '987 Easy Street', 'President'

[table2]
person_id, nameindex, nametype, nameval
1, 1, 'AB', 1
1, 2, 'BA', 1
2, 1, 'BB', 2

Thanks for great help , yes its working for me.

Thanks to all for taking time to explain me.

Regards

[quoted text, click to view]