Delete Statement with inner join in Sql Server 2005

   Tweet

Most of us working in sql server doesn't use delete statement with "inner join" clause.
We normal use two or three query to delete the require rows, which can be minimised by using "inner join" clause in delete statement on sql server.

Sample input table

create table #movies(movieid int identity(1,1),movietype varchar(20),moviename varchar(20))
GO
insert into #movies values('Hollywood Movie','Titanic');
insert into #movies values('Bollywood Movie','Dil Se');
insert into #movies values('Tamil Movie','Basha');
insert into #movies values('Telugu Movie','Okkadu');
GO
select * from #movies
GO
create table #theatre(theatreid int identity(1,1),movieid int,theatrename varchar(20))
GO
insert into #theatre values(1,'Fox Theatres');
insert into #theatre values(2,'Adlabs');
insert into #theatre values(3,'Satyam Cinemas');
insert into #theatre values(4,'INOX');
GO
select * from #theatre
GO

Sample query to use delete statement with inner join in sql server

delete from #theatre from #movies m inner join #theatre t on 
t.movieid=m.movieid where m.moviename='Okkadu'

Syntax for delete statement with inner join

delete from <tablename> from <tablename> inner join <tablename1> 
on <tablename>.column1=<tablename1>.column2

   Tweet