Deleting Duplicating Data - 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

General SQL Server Forums

New to SQL Server Programming

Deleting Duplicating Data

Author

Topic

aisha09
Starting Member

6 Posts

Posted - 2010-07-16 : 16:27:52

Table a has Vendor A data
table b has vendor B and VendorA data

I need to delete Vendor A data from table B which is present in Table A.

Both the vndid are supposed to be the common field. But there
is a lot of mismatch in the data;

for example vndid of Cheryl Smith is 0004 in Table A
but the vndid of Cheryl Smith in Table B shows 00004

could you please help me with this query.

Is der anywy we could do this with SSIS; as I am dealing with thousands of records

ThankYou
Aisha

Table A

vndid vndname
2 Smith R
4 julia
5 nick
6 dalia
Null dale

Table B

vndid vndname
2 R smith
7 Cheryl
4 Julia
5 Nick
6 Dalia
9 MARY
10 dale
11 joshua

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-07-16 : 17:39:14

something like:

DELETE 
	B
FROM 
	TableB AS B
INNER JOIN
	TableA AS A
	ON B.vndid = A.vndid

Sure you can also do this with SSIS in a number of ways.

aisha09
Starting Member

6 Posts

Posted - 2010-07-16 : 18:20:28

Hi i already deleted rows with the common vndid, but many records have vndid as null so i need to compare the two tables based on the vendorname. This is what i did. I created a table to concatenate the last name and firstname in table A.

insert into C(name,vendorid)
SELECT FirstName + ' ' + LastName as name, vendorid FROM A
ORDER BY FirstName ASC;
-------------------------------------------
delete from B where vendorname in
(
select name from C

join B on C.name = b.vendorname )

But there seems to be no use. Please help.

CrazyT
Yak Posting Veteran

73 Posts

Posted - 2010-07-16 : 19:52:19

you have a "smith r" and and "r smith" in the other table -- that a typo or are the fields you comparing backwards?

aisha09
Starting Member

6 Posts

Posted - 2010-07-16 : 20:11:14

Thats not a typo. We gt the data in that format; with so much if differences in the name; Actually its the same name but written backwards.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources