DELETE using INNER JOIN

  • JMI

    SSChampion

    Points: 11831

    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

  • Santhi Indukuri

    SSC Enthusiast

    Points: 191

    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 )

  • Rajat Jaiswal-337252

    SSCommitted

    Points: 1779

    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 🙂

  • JMI

    SSChampion

    Points: 11831

    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

  • Rajat Jaiswal-337252

    SSCommitted

    Points: 1779

    Hey ,

    That's great !

    Thanks a lot to for appriciation.

    😎

  • Korrapati

    SSC Veteran

    Points: 224

    This is good.

  • FootyRef

    Right there with Babe

    Points: 723

    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.

  • John Mitchell-245523

    SSC Guru

    Points: 148259

    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

  • FootyRef

    Right there with Babe

    Points: 723

    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.

  • FootyRef

    Right there with Babe

    Points: 723

    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.

  • FootyRef

    Right there with Babe

    Points: 723

    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.

  • inception

    SSC Rookie

    Points: 47

    Hello,

    I'm quite new to SQL. Is there any way someone could re-write the statement posted above, to generic terms? i.e., with Table 1, Table 2, Column 1, etc.

    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"

    thanks all!

  • Vikash Anil

    Valued Member

    Points: 74

    Use following

    DELETE ord

    FROM Orders ord

    INNER JOIN Customers c(nolock)

    ON ord.CustomerID = c.CustomerID

    WHERE c.FirstName = 'vikash'

    regards,

    blog.learnonlinewithme.com

  • das_bhasker

    SSC Rookie

    Points: 47

    Thanks Rajat, Your solutions saved my hours.

    Thanks again 🙂

  • sagscorp22

    Valued Member

    Points: 67

    I know. It's old post still thanks.

    I was doing delete from too.

    Your reply helped.

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply