DELETE using INNER JOIN

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » DELETE using INNER JOIN

21 posts, Page 1 of 3

1 2 3 »»»

DELETE using INNER JOIN

Rate Topic

Display Mode

Topic Options

Author

Message

JMI

Posted Thursday, May 01, 2008 2:45 PM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, June 28, 2011 12:06 PM
Points: 557, Visits: 775

if exists (select * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#prodextend'))
DROP TABLE #prodextend;
if exists (select * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#product'))
DROP TABLE #product;
if exists (select * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#manu_clients'))
DROP TABLE #manu_clients;

create table #prodextend(din varchar(5),pkgsize int)
insert #prodextend (din,pkgsize) values ('12',6652)
insert #prodextend (din,pkgsize) values ('13',7735)

create table #product(din varchar(5),pkgsize int,client char(3))
insert #product(din,pkgsize,client) values('12',6652,'GSK')
insert #product(din,pkgsize,client) values('13',7735,'BBN')

create table #manu_clients(clientcode char(3))
insert #manu_clients(clientcode) values('GSK')
insert #manu_clients(clientcode) values('SDM')

select * from #prodextend
select * from #product
select * from #manu_clients

======================================

Could anybody write a nice DELETE statement
to:
delete records from #prodextend
where din and pkgsize exist in #product
where #product.client = #manu_clients.clientcode

Post #493913

Santhi Indukuri

Posted Thursday, May 01, 2008 3:08 PM

Valued Member

Group: General Forum Members
Last Login: Monday, October 22, 2012 1:24 PM
Points: 59, Visits: 88

Check this..

delete PE
from #prodextend as PE
where exists (SELECT 1 FROM #product PT
INNER JOIN #manu_clients MC
ON PT.client = MC.clientcode
WHERE PE.Din = PT.Din and PE.Pkgsize=PT.pkgsize )

Post #493927

Rajat Jaiswal-337252

Posted Friday, May 02, 2008 5:09 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 4:43 PM
Points: 110, Visits: 289

Hi,
try this
DELETE px
FROM #prodextend px
INNER JOIN #product p ON p.din = px.din
AND p.pkgSize = px.pkgSize
INNER JOIN #manu_clients mc ON mc.clientCode = p.clientCode
Regards
Rajat :)

Post #494124

JMI

Posted Friday, May 02, 2008 6:27 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, June 28, 2011 12:06 PM
Points: 557, Visits: 775

Thank you guys.
Both DELETE statements work.
But the one Rajat wrote is easier to understand for me.
Thank you Rajat.

I knew it was possible to do DELETE using JOIN
I just couldn't figure out that you should
do
DELETE t1 FROM Table1 t1 INNER JOIN ..
not
DELETE FROM Table1 INNER JOIN
(that's what I tried)

Thanks,
Rob

Post #494163

Rajat Jaiswal-337252

Posted Sunday, May 04, 2008 3:00 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 4:43 PM
Points: 110, Visits: 289

Hey ,
That's great !
Thanks a lot to for appriciation.
Cool

Post #494684

Korrapati

Posted Friday, June 19, 2009 1:27 PM

Grasshopper

Group: General Forum Members
Last Login: Thursday, August 25, 2011 3:38 PM
Points: 12, Visits: 47

This is good.

Post #738601

FootyRef

Posted Wednesday, July 08, 2009 8:44 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, May 10, 2013 5:57 AM
Points: 11, Visits: 28

Many thanks for both suggestions, as well. I, too, found Rajat's easier to understand and it worked correctly as:

DELETE a
FROM "dbo"."DM_ConformedPerson" a
JOIN "dbo"."TST_Fix_DM_ConformedPerson" z
ON a."Person_SK" = z."Person_SK"
WHERE z."Person_SK" <> z."MaxSK"

Because of this solution, I have now joined this web site to hopefully easily find solutions such as this in the future.

Post #749332

John Mitchell-245523

Posted Thursday, July 09, 2009 7:42 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 3:21 AM
Points: 4,420, Visits: 7,176

Scott

I'm pleased that you found your answer, although I notice that the final query you showed doesn't look much like your original requirement!

I'd encourage you not to put object names and schema names between inverted commas, for the following reasons:
(1) It's less readable, and not necessary. If one of your objects contains a space or other unpermitted special character, or shares its name with a keyword (this doesn't apply to anything in your query) then you should use square brackets instead, for the reason below.
(2) If SET QUOTED_IDENTIFIER is OFF, your query will fail.

John

Post #750220

FootyRef

Posted Thursday, July 09, 2009 8:33 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, May 10, 2013 5:57 AM
Points: 11, Visits: 28

I was not the one who originally asked the question. I just searched and found this answer to a problem I was having here deleting records using a JOIN. I am using the SQLTerm tool within Cognos Data Manager and that was how the SQL was formed when the tool did it, so I just cut and pasted it into this thread to show the solution to my issue.

Post #750292

FootyRef

Posted Thursday, July 09, 2009 8:34 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, May 10, 2013 5:57 AM
Points: 11, Visits: 28

Post #750294

« Prev Topic | Next Topic »

21 posts, Page 1 of 3

1 2 3 »»»

Permissions