I am having difficulty trying to figure out how to compare two integers stored in a table to return a third. I have two integer fields in one table and two in another like this:
Table1.SomeNumber1 = 1
Table1.SomeNumber2 = 2
Table2.SomeNumber1 = 2
Table2.SomeNumber2 = 1
I need to be able to compare the first number from the first table to the first number in the second table. If the values are different I need to set a variable or field to 0. If the numbers are the same I need to set my variable or field to 1.
I need to follow the same procedure comparing the second number in the first table to the second number in the second table. In addition, I need to be able to do it in a single select statement.
Does anyone have any ideas on how this could be done? Thank you for any help you may be able to provide.
Gmz
CREATE TABLE [dbo].[num1]([id] [int] NULL,
[number1] [int] NULL,
[number2] [int] NULL,
[flag1] [int] NULL,
[flag2] [int] NULL
)
CREATE TABLE [dbo].[num2](
[id] [int] NULL,
[number1] [int] NULL,
[number2] [int] NULL
)
--After the comparison, two flag fields in table num1 are updated
UPDATE num1
SET flag1=(SELECT (CASE WHEN a.number1 = b.number1 THEN 1 ELSE 0 END) FROM num1 a INNER JOIN
num2 b ON a.id = b.id and a.id=c.id), flag2=(SELECT (CASE WHEN a.number2 = b.number2 THEN 1 ELSE 0 END) FROM num1 a INNER JOIN
num2 b ON a.id = b.id and a.id=c.id)
FROM num1 c|||
I took what you posted and applied it to just return the values of the comparisons as results of my query which saved me from having to store the extra data in the table. It worked great.
Thank you!!!!
GMZ
sqlsql
ly basis.