August 6, 2009 at 7:11 am
Hello,
Below is a script that can be used to generate the sample tables and data:
use Test
--===== If the test table already exists, drop it
IF OBJECT_ID('TableA','U') IS NOT NULL
DROP TABLE TableA
IF OBJECT_ID('TableB','U') IS NOT NULL
DROP TABLE TableB
--===== Create the test table with
CREATE TABLE TableA
(
ID1 INT PRIMARY KEY,
Value1 INT
)
CREATE TABLE TableB
(
ID2 INT PRIMARY KEY,
Value2 INT
)
--===== Insert the test data into the test table
INSERT INTO TableA
(ID1, Value1)
SELECT '4','1' UNION ALL
SELECT '37','8' UNION ALL
SELECT '44','11' UNION ALL
SELECT '54','2'
INSERT INTO TableB
(ID2, Value2)
SELECT '37','' UNION ALL
SELECT '44','12' UNION ALL
SELECT '54',''
I want a query which would search ID1 of TableA in ID2 of TableB. If there is a match and if Value2 of that matched record in TableB is 0 then it replaces value2 with the Value1 ELSE if value2 is not 0 and value1 is not equal to value2 for that record then display a error message with the ID1 or ID2 number in it(since they both will be same for this record).
For eg: It will try to find 4(ID1) of TableA in ID2 field of TableB. Since its not peresnt, it will move on to the next record. Now it will move to next record, since there is a match it will set value2=8 for the record with ID2=37. And for ID=44, It will display and error message with ID number printed in it.
Thanks for the help
August 6, 2009 at 7:19 am
I wrote this query but it goes row by row:
IF ( members.recipid = memcaid.recipid)
BEGIN
IF(members.royalid IS NULL)
BEGIN
UPDATE members
SET members.royalid = CAST(memcaid.membid AS varchar(12))
END
ELSE
BEGIN
IF(members.royalid CAST(memcaid.membid AS varchar(12)))
BEGIN
PRINT 'Mismatch';
END
END
END
Should I do this in two seperate queries? One query for the inner if part and the second query for the inner else?
August 6, 2009 at 8:45 am
I am not sure this is what you are looking for but here it is: Let me know if you need this in a different format.
//EDIT Also, are you sure value2 needs to be = 0 or should it be that value2 is null?
--===== If the test table already exists, drop it
IF OBJECT_ID('TableA','U') IS NOT NULL
DROP TABLE TableA
IF OBJECT_ID('TableB','U') IS NOT NULL
DROP TABLE TableB
--===== Create the test table with
CREATE TABLE TableA
(
ID1 INT PRIMARY KEY,
Value1 INT
)
CREATE TABLE TableB
(
ID2 INT PRIMARY KEY,
Value2 INT
)
--===== Insert the test data into the test table
INSERT INTO TableA
(ID1, Value1)
SELECT '4','1' UNION ALL
SELECT '37','8' UNION ALL
SELECT '44','11' UNION ALL
SELECT '54','2'
INSERT INTO TableB
(ID2, Value2)
SELECT '37','' UNION ALL
SELECT '44','12' UNION ALL
SELECT '54',''
UPDATE B
SET Value2 = A.value1
FROM TableB b
INNER JOIN TableA A ON (A.ID1 = b.ID2 AND b.Value2 = 0)
SELECT A.ID1, 'MISMATCH' from
tableA A INNER JOIN
TABLEB B ON (a.id1 = b.id2 AND A.Value1 B.Value2)
August 6, 2009 at 8:58 am
Matt Wilhoite (8/6/2009)
I am not sure this is what you are looking for but here it is: Let me know if you need this in a different format.//EDIT Also, are you sure value2 needs to be = 0 or should it be that value2 is null?
--===== If the test table already exists, drop it
IF OBJECT_ID('TableA','U') IS NOT NULL
DROP TABLE TableA
IF OBJECT_ID('TableB','U') IS NOT NULL
DROP TABLE TableB
--===== Create the test table with
CREATE TABLE TableA
(
ID1 INT PRIMARY KEY,
Value1 INT
)
CREATE TABLE TableB
(
ID2 INT PRIMARY KEY,
Value2 INT
)
--===== Insert the test data into the test table
INSERT INTO TableA
(ID1, Value1)
SELECT '4','1' UNION ALL
SELECT '37','8' UNION ALL
SELECT '44','11' UNION ALL
SELECT '54','2'
INSERT INTO TableB
(ID2, Value2)
SELECT '37','' UNION ALL
SELECT '44','12' UNION ALL
SELECT '54',''
UPDATE B
SET Value2 = A.value1
FROM TableB b
INNER JOIN TableA A ON (A.ID1 = b.ID2 AND b.Value2 = 0)
SELECT A.ID1, 'MISMATCH' from
tableA A INNER JOIN
TABLEB B ON (a.id1 = b.id2 AND A.Value1 B.Value2)
Thanks so much for the help. So my guess was right, we do need 2 queries for this. one to update and other to just select the erronious record. Which means internally the SQL server wi;; have to parse the records in the 2 table twice (once for update and second time for select).
Thanks for catching that error on my part 😛 Its supposed to be null. So I guess just replace b.value = 0 but b.value2 IS NULL right?
August 6, 2009 at 8:59 am
novice_coder (8/6/2009)
Matt Wilhoite (8/6/2009)
I am not sure this is what you are looking for but here it is: Let me know if you need this in a different format.//EDIT Also, are you sure value2 needs to be = 0 or should it be that value2 is null?
--===== If the test table already exists, drop it
IF OBJECT_ID('TableA','U') IS NOT NULL
DROP TABLE TableA
IF OBJECT_ID('TableB','U') IS NOT NULL
DROP TABLE TableB
--===== Create the test table with
CREATE TABLE TableA
(
ID1 INT PRIMARY KEY,
Value1 INT
)
CREATE TABLE TableB
(
ID2 INT PRIMARY KEY,
Value2 INT
)
--===== Insert the test data into the test table
INSERT INTO TableA
(ID1, Value1)
SELECT '4','1' UNION ALL
SELECT '37','8' UNION ALL
SELECT '44','11' UNION ALL
SELECT '54','2'
INSERT INTO TableB
(ID2, Value2)
SELECT '37','' UNION ALL
SELECT '44','12' UNION ALL
SELECT '54',''
UPDATE B
SET Value2 = A.value1
FROM TableB b
INNER JOIN TableA A ON (A.ID1 = b.ID2 AND b.Value2 = 0)
SELECT A.ID1, 'MISMATCH' from
tableA A INNER JOIN
TABLEB B ON (a.id1 = b.id2 AND A.Value1 B.Value2)
Thanks so much for the help. So my guess was right, we do need 2 queries for this. one to update and other to just select the erronious record. Which means internally the SQL server will have to parse the records in the 2 table twice (once for update and second time for select).
Thanks for catching that error on my part 😛 Its supposed to be null. So I guess just replace b.value = 0 by b.value2 IS NULL right?
August 6, 2009 at 9:02 am
Yeah, that replacement should do it.
August 6, 2009 at 9:04 am
Thanks so much.
Cheers
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply