Compare Tables with compound PK

Compare Tables with compound PK

Would like to know the correct way to compare two tables that has a Compound Primary Key.
The idea is, I have tables with the same structure, i need to delete from one the rows that already have
in the anotherone, but have primary keys with more than one column.
I'm doing this way, but not sure if there are any other best. '

Delete Table1
Where Ltrim(Rtrim(Convert(Char(20),code))) +
Ltrim(Rtrim(Convert(Char(20),period))) +
Ltrim(Rtrim(Convert(Char(20),named)))

In ( Select Ltrim(Rtrim(Convert(Char(20),code))) +
Ltrim(Rtrim(Convert(Char(20),period))) +
Ltrim(Rtrim(Convert(Char(20),named)))
From ##Table2)

Thanks in Advance!

: )

Code Snippet

Delete tb1

from Table1 tb1

inner join ##Table2 tb2

on tb1.code = tb2.code

and tb1.period = tb2.period

and tb1.named = tb2.named

|||

Try using EXISTS operator.

delete dbo.t1

where exists (

select *

from dbo.t2

where dbo.t2.c1 = dbo.t1.c1

and dbo.t2.c2 = dbo.t1.c2

dbo.t2.c3 = dbo.t1.c3

)

go

AMB

|||

Thank you so much,

Let me ask you something more just to understand clearly the convenience of not to do it the first way.. is it in terms of performance or precision a wrong way to use concatenation? My question is because I have to do it for many tables and I would like to do it correctly.

|||

String catenation incurs a substantial performance hit -and will most likely also be unable to utilize any indexing. And to require if for every row in the table JUST in order to accomplish this JOIN is totally unnecessary.

Alejandro's suggestion of using the [ IF EXISTS ] structure is the most efficient, since the DELETE will occur upon finding the first row that meets the criteria, whereas Dale's suggestion will have to traverse through the indexes to see if there are more than one row that meets the criteria.

|||ok, Thank you, so much : )