Post reply

DELETE using INNER JOIN

  • May 1, 2008 at 2:45 pm

    #206940

    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

  • May 1, 2008 at 3:08 pm

    #809831

    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 )

  • May 2, 2008 at 5:09 am

    #809989

    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 🙂

  • May 2, 2008 at 6:27 am

    #810024

    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

  • May 4, 2008 at 3:00 am

    #810449

    Hey ,

    That's great !

    Thanks a lot to for appriciation.

    😎

  • June 19, 2009 at 1:27 pm

    #1012637

    This is good.

  • July 8, 2009 at 8:44 am

    #1021725

    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.

  • July 9, 2009 at 7:42 am

    #1022359

    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

  • July 9, 2009 at 8:33 am

    #1022409

    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.

  • July 9, 2009 at 8:34 am

    #1022410

    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.

  • July 9, 2009 at 8:35 am

    #1022417

    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.

  • August 5, 2010 at 9:42 am

    #1202871

    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!

  • October 8, 2010 at 3:23 pm

    #1234553

    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

  • June 9, 2011 at 1:59 pm

    #1336706

    Thanks Rajat, Your solutions saved my hours.

    Thanks again 🙂

  • September 16, 2011 at 10:58 am

    #1382681

    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