Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

DELETE using INNER JOIN Expand / Collapse
Author
Message
Posted Thursday, May 1, 2008 2:45 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr 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
Posted Thursday, May 1, 2008 3:08 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, March 27, 2014 6:56 PM
Points: 59, Visits: 102
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
Posted Friday, May 2, 2008 5:09 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 16, 2014 3:59 AM
Points: 110, Visits: 331
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
Posted Friday, May 2, 2008 6:27 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr 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
Posted Sunday, May 4, 2008 3:00 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 16, 2014 3:59 AM
Points: 110, Visits: 331
Hey ,
That's great !
Thanks a lot to for appriciation.
Post #494684
Posted Friday, June 19, 2009 1:27 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 25, 2011 3:38 PM
Points: 12, Visits: 47
This is good.
Post #738601
Posted Wednesday, July 8, 2009 8:44 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 6:00 AM
Points: 13, Visits: 49
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
Posted Thursday, July 9, 2009 7:42 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:48 AM
Points: 5,415, Visits: 10,069
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
Posted Thursday, July 9, 2009 8:33 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 6:00 AM
Points: 13, Visits: 49
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
Posted Thursday, July 9, 2009 8:34 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 6:00 AM
Points: 13, Visits: 49
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 #750294
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse