473,230 Members | 1,500 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,230 software developers and data experts.

how to compare value of two fileds and based on that insert value into third fileds

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

Jan 30 '07 #1
8 6177
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
Jan 30 '07 #2
On Jan 30, 6:47 pm, "Plamen Ratchev" <Pla...@SQLStudio.comwrote:
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 Ratchevhttp://www.SQLStudio.com

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

Jan 31 '07 #3
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
Jan 31 '07 #4
On Jan 31, 11:42 am, "Plamen Ratchev" <Pla...@SQLStudio.comwrote:
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

Jan 31 '07 #5
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 :)

Jan 31 '07 #6
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

Jan 31 '07 #7
Tradeorganizer wrote:
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 .
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
2, 2, 'BB', 2
Feb 1 '07 #8
Thanks for great help , yes its working for me.

Thanks to all for taking time to explain me.

Regards

Ed Murphy wrote:
Tradeorganizer wrote:
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 .

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
2, 2, 'BB', 2
Feb 7 '07 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Neil | last post by:
Folks, I've got a problem at work, and I'm hoping that someone out there may have had something similar (although I doubt it!) or may be able to offer some advice. I'm in the process of...
10
by: jqq | last post by:
SQL2K on W2Kserver I need some help revamping a rather complicated query. I've given the table and existing query information below. (FYI, changing the database structure is right out.) The...
1
by: Dan | last post by:
This is one that has me stumped and I need an expert's input. Any ideas why the values from the second script-generated drop down list isn't recognized by the script to add time values to the...
122
by: Einar | last post by:
Hi, I wonder if there is a nice bit twiddling hack to compare a large number of variables? If you first store them in an array, you can do: for (i = 0; i < n; i++) { if (array != value) {...
2
by: Billy | last post by:
Change DataGrid EditControl On Data Value Hi, I have a datagrid, and on editing, I want to change the control in the third colunm based on the value of the first column. The value in the...
10
by: NewToCPP | last post by:
I am having problem with key compare in stl map. Below is part of my code .. could anyone tell me what might be wrong here... I am using VC++ 6.0 code: ===== class MyKey {
5
by: Tradeorganizer | last post by:
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)
5
by: S S | last post by:
Hi I have a requirement where I am declaring a map within a class. class abc { map <void*, void*mMap; // I do not pass compare struct here. .... }; Here I am not passing compare function,...
3
by: zlwilly | last post by:
Hi everyone! I have written two classes, Fraction and useFraction. Fraction holds all of the methods required to process the data from useFraction, which consists of private ints that are turned...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.