473,239 Members | 1,566 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,239 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
5 1581
Tradeorganizer wrote:
Hi,

I have a database
What database? Type and version please!
with table name as test in that i have 6 colums
they are

name varchar (20)
OK, I will assume SQL Server
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
So if they are equal, set it to 2, not equal, set it to 1? This is easy
using CASE

UPDATE Test
SET nameval =
CASE WHEN nametype1=nametype2 then 2 ELSE 1 END
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
Jan 30 '07 #2
On Jan 30, 4:54 pm, "Bob Barrows [MVP]" <reb01...@NOyahoo.SPAMcom>
wrote:
Tradeorganizer wrote:
Hi,
I have a database

What database? Type and version please!
with table name as test in that i have 6 colums
they are
name varchar (20)

OK, I will assume SQL Server


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

So if they are equal, set it to 2, not equal, set it to 1? This is easy
using CASE

UPDATE Test
SET nameval =
CASE WHEN nametype1=nametype2 then 2 ELSE 1 END

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

thanks a lot budy it worked......great...wish you good luck

Regards

Jan 30 '07 #3
Why are you storing a calculated value in the table to begin with?

Wouldn't it be better to do that on the output?

Bob Lehmann

"Tradeorganizer" <tr************@gmail.comwrote in message
news:11********************@v33g2000cwv.googlegrou ps.com...
On Jan 30, 4:54 pm, "Bob Barrows [MVP]" <reb01...@NOyahoo.SPAMcom>
wrote:
Tradeorganizer wrote:
Hi,
I have a database
What database? Type and version please!
with table name as test in that i have 6 colums
they are
name varchar (20)
OK, I will assume SQL Server


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
So if they are equal, set it to 2, not equal, set it to 1? This is easy
using CASE

UPDATE Test
SET nameval =
CASE WHEN nametype1=nametype2 then 2 ELSE 1 END

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


thanks a lot budy it worked......great...wish you good luck

Regards

Jan 30 '07 #4
True.
Although the way he described it seems to disqualify this explanation,
maybe he has to store historical data.

Bob Lehmann wrote:
Why are you storing a calculated value in the table to begin with?

Wouldn't it be better to do that on the output?

Bob Lehmann
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
Jan 30 '07 #5
On Jan 30, 6:33 pm, "Bob Barrows [MVP]" <reb01...@NOyahoo.SPAMcom>
wrote:
True.
Although the way he described it seems to disqualify this explanation,
maybe he has to store historical data.

Bob Lehmann wrote:
Why are you storing a calculated value in the table to begin with?
Wouldn't it be better to do that on the output?
Bob Lehmann

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.


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 #6

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...
1
by: vj | last post by:
How i can populate all fileds dynamically in jsp page based on contents found in xml file? I have written jsp servlets and java class file. i transferred automatic data from jsp to servlet then to...
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 {
8
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...
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...
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: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
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...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

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.