SQL Comparision - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

SQL Comparision

Author

Topic

Srikanth.rk
Starting Member

1 Post

Posted - 2012-08-24 : 12:19:47

Hello i am having hard time with this query.

I have a table in which i have the following information.

ID ACCT
1 A
1 B
1 E
2 A
2 C
2 D

Here the ID and ACCT combination is a composite key.
I would like to update the ID 1 with ID 2 for accts B and E but not for account A. As that will violate the primary key.

SO i created two cursor loops one read all the accts of id 1 and other for ID 2

so for every ID account combination of 1 is compared with that of 2.
while 2 being the outer loop and 1 is the inner loop. But i am still not able to achieve the results as i am just trying to print when match is found and do an update when no match is found.

Hers is the code. Please help me .

-- THIS IS TO OPEN THE RECORD SET FOR BAD Id

DECLARE BadAccts CURSOR
FOR
SELECT Acct_Cd FROM table WHERE ID = @BadID ORDER BY Acct_Cd

-- OPEN THE BAD SECURITY RECORD SET
OPEN BadAccts

-- TRY READING THE RECORD SET TO GET THE STATUS
FETCH NEXT FROM BadAccts INTO @BadAcct

-- OUTER LOOP TO READ RECORD SET FOR BAD ID
-- AND COMPARE WITH EACH RECORD OF GOOD ID
WHILE @@FETCH_STATUS = 0
BEGIN
-- TO OPEN THE RECORD SET FOR GOOD ID
DECLARE GoodAccts CURSOR
FOR
SELECT Acct_Cd FROM table WHERE ID = @GoodID ORDER BY Acct_Cd

-- OPEN THE GOOD SECURITY RECORD SET.
OPEN GoodAccts

FETCH NEXT FROM GoodAccts INTO @GoodAcct

---- INNER LOOP TO FETCH RECORD FOR GOOD ID.
WHILE @@FETCH_STATUS = 0
BEGIN
IF (LTRIM(RTRIM(@GoodAcct)) = LTRIM(RTRIM(@BadAcct)))
BEGIN
PRINT 'ACCTS MATCH'
BREAK;
END
ELSE
BEGIN
PRINT 'ACCTS DIDNT MATCH'

---- UPDATE THE BAD ID WITH GOOD ID
---- IF THE ID ACCOUNT COMBINATION OF BAD ID DOESNT MATCH WITH THAT OF GOOD ID
UPDATE
Table
SET
Id = @Goodid
WHERE
Sec_Id = @Badid
AND
Acct_cd = @BadAcct
END;

---- READING RECORDS FOR GOOD ID
FETCH NEXT FROM GoodAccts INTO @GoodAcct
END;

CLOSE GoodAccts
DEALLOCATE GoodAccts

FETCH NEXT FROM BadAccts INTO @BadAcct

END;

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-24 : 13:17:23

I must admit that I didn't read through your code. The following is simply based on your description which says:

quote:
I would like to update the ID 1 with ID 2 for accts B and E but not for account A. As that will violate the primary key.

. If that is the case, you can use a set-based approach like this:

CREATE TABLE #tmp (ID INT, ACCT VARCHAR(32));
INSERT INTO #tmp VALUES 
(1,'A'),
(1,'B'),
(1,'E'),
(2,'A'),
(2,'C'),
(2,'D');


UPDATE t SET
	ID = 2
FROM 
	#tmp t
WHERE
	ID = 1
	AND NOT EXISTS
	(
		SELECT * FROM #tmp t2
		WHERE t2.ACCT=t.ACCT
		AND t2.ID = 2
	);

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources