DELETE using INNER JOIN

  • 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

  • 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 )

  • 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 🙂

  • 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

  • Hey ,

    That's great !

    Thanks a lot to for appriciation.

    😎

  • This is good.

  • 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.

  • 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

  • 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.

  • 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.

  • 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.

  • 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!

  • 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

  • Thanks Rajat, Your solutions saved my hours.

    Thanks again 🙂

  • I know. It's old post still thanks.

    I was doing delete from too.

    Your reply helped.

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

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