IF EXISTS Function - 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 2000 Forums

SQL Server Development (2000)

IF EXISTS Function

Author

Topic

oahu9872
Posting Yak Master

112 Posts

Posted - 2005-09-12 : 11:28:27

I need to set up a sql statement that will compare data in two tables, and erase data that does not match. I would start with something like this:

Table A
(AccountID)
1
2
3
4
5

Table B
(AccountID)
1
2
3
4
5
6
7

Table A is my primary table. I need a function that will tell Table A to compare itself to Table B, and delete any rows that do not match. In this case, the output would look like this:

Table A
(AccountID)
1
2
3
4
5

Table B
(AccountID)
1
2
3
4
5

AccountID's 6 and 7 were dropped from Table B because they did not exist in Table A.

Is there a way to use the EXISTS function to compare two tables like this.

Thank you.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-09-12 : 11:48:35

A simple DELETE statement should do what you want:


delete from TableB
where
	TableB.AccountID not in
	(
	select
		TableA.AccountID
	from
		TableA
	where
		TableA.AccountID is not null
	)

CODO ERGO SUM

oahu9872
Posting Yak Master

112 Posts

Posted - 2005-09-12 : 11:58:10

That works nice. Thanks alot.

hankswart@hotmail.com
Starting Member

2 Posts

Posted - 2005-09-12 : 12:31:55

The "IN" clause can only be used with a single column comparison so if you wan't to use a multiple column comparison here's the code.

DELETE FROM TableB
FROM TableB A
WHERE
NOT EXISTS
(
SELECT
B.AccountID,
B.ColumnB,
B.ColumnC
FROM
tableA B
WHERE
(B.AccountID = A.AccountID) AND
(B.ColumnB = A.ColumnB) AND
(B.ColumnC = A.ColumnC)
)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources