Scrubbing out phone matching phone numbers

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)

Scrubbing out phone matching phone numbers

Author

Topic

RBradley
Starting Member

1 Post

Posted - 2012-08-10 : 13:47:36

I need a script that will allow me to compare phone mumbers in two different tables and remove the matching numbers from one of the tables. I have a table that has a do not call list and I have a table of phone numbers I would like to call. So, I want to compare the phone numbers in both tables and remove any that match in the table that contains the numbers I want to call. It seems so easy until I try to do it. I am new to SQL, so be as specific as you can.

Thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-10 : 13:53:17

Assuming the numbers are in identical format, you can get all the matching rows in CallList table like this:

SELECT
	c.*
FROM
	CallList c
	INNER JOIN DonotCall d ON d.PhoneNumber = c.PhoneNumber;

This should be equal to or less than the total number of rows in the DoNotCall table. Once you are satisfied that that is correct, you can delete it like this:

DELETE c
FROM
	CallList c
	INNER JOIN DonotCall d ON d.PhoneNumber = c.PhoneNumber;

Everything after the FROM clause should be identical in the two queries.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-08-10 : 13:54:50

[code]
;with cteRemovePhones(PhoneNumberRowID)
AS
(
SELECT PhoneNumberRowID
FROM dbo.Mumbo a
inner join dbo.Jumbo b
on a.PhoneNumber = b.PhoneNumber
)
DELETE FROM Mumbo
WHERE PhoneNumberRowID IN
(SELECT PhoneNumberRowID
FROM cteRemovePhones
);
[/code]

maybe something like this. run in test first

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources