How to do a join in a delete  
Author Message
unreal_address2





PostPosted: Mon Feb 23 06:06:17 CST 2004 Top

SQL Server Developer >> How to do a join in a delete

I want to delete records in tableA based on a join with tableB as follows:


delete Tablea
Where TableA.Col1 = TableB.Col1 and
TableA.COl2 = TableB.Col2 And
TableA.Col3 = TableB.Col3 And
TableB.DeleteFlag = 'Y'

How can I accomplish this kind of a delete can you please provide some tips.

Thanks
Belinda

SQL Server292  
 
 
Uri





PostPosted: Mon Feb 23 06:06:17 CST 2004 Top

SQL Server Developer >> How to do a join in a delete Belinda
delete Tablea FROM
Tablea join Tableb ON TableA.Col1 = TableB.Col1 and
TableA.COl2 = TableB.Col2 And
TableA.Col3 = TableB.Col3 And
TableB.DeleteFlag = 'Y'



> I want to delete records in tableA based on a join with tableB as follows:
>
>
> delete Tablea
> Where TableA.Col1 = TableB.Col1 and
> TableA.COl2 = TableB.Col2 And
> TableA.Col3 = TableB.Col3 And
> TableB.DeleteFlag = 'Y'
>
> How can I accomplish this kind of a delete can you please provide some
tips.
>
> Thanks
> Belinda


 
 
Andrew





PostPosted: Mon Feb 23 06:12:55 CST 2004 Top

SQL Server Developer >> How to do a join in a delete Belinda,

Easier with aliases

Delete a
from TableA as a -- the "as" is actually optional
inner join TableB b
on b.Col1 = a.Col1
and b.Col3 = a.Col3
and b.DeleteFlag = 'Y'

The where clause is an obsolete place to do joins now.
The beauty of this syntax is that you can first to a Select
to see what you are going to delete by only changing the first line
to Select *

Regards
AJ



> I want to delete records in tableA based on a join with tableB as follows:
>
>
> delete Tablea
> Where TableA.Col1 = TableB.Col1 and
> TableA.COl2 = TableB.Col2 And
> TableA.Col3 = TableB.Col3 And
> TableB.DeleteFlag = 'Y'
>
> How can I accomplish this kind of a delete can you please provide some tips.
>
> Thanks
> Belinda


 
 
Lasse





PostPosted: Mon Feb 23 08:33:26 CST 2004 Top

SQL Server Developer >> How to do a join in a delete Belinda,

http://www.winnetmag.com/SQLServer/Article/ArticleID/8808/8808.html

/Lasse




> I want to delete records in tableA based on a join with tableB as follows:
>
>
> delete Tablea
> Where TableA.Col1 = TableB.Col1 and
> TableA.COl2 = TableB.Col2 And
> TableA.Col3 = TableB.Col3 And
> TableB.DeleteFlag = 'Y'
>
> How can I accomplish this kind of a delete can you please provide some
tips.
>
> Thanks
> Belinda