match records between 2 tables where they dont exi

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

General SQL Server Forums

New to SQL Server Programming

match records between 2 tables where they dont exi

Author

Topic

jatrix32
Starting Member

49 Posts

Posted - 2010-09-14 : 13:15:48

I have a subscription table and a phonenumbers table

I need to delete all the phonenumbers out of the phonenumber table where they do not exist in the subscription table.

Basically users phonenumbers are stored in both tables, and someone deleted them out of the subscription table but not out of the phonenumber table. I cant figure out how to do this.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-09-14 : 13:36:41

[code]DELETE pn
FROM phoneNumbers pn
LEFT JOIN
subscriptions s
On s.phoneNumber = pn.phoneNumber
WHERE s.phoneNumber IS NULL;[/code]

jatrix32
Starting Member

49 Posts

Posted - 2010-09-14 : 13:40:11

that works, thanks!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-16 : 12:59:16

also


DELETE	pn
FROM	phoneNumbers pn
WHERE NOT EXISTS
(SELECT 1 FROM	subscriptions s
WHERE	s.phoneNumber = pn.phoneNumber)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-09-16 : 14:51:48

Or this (if using SQL Server 2008)

MERGE	PhoneNumber AS tgt
USING	Subsriptions AS src ON src.PhoneNumber = tgt.PhoneNumber
WHEN	NOT MATCHED BY SOURCE
		THEN	DELETE;

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources