Post reply

I want to delete those data from tables Orders and customers which is not selecting in below join

  • July 23, 2012 at 2:28 am

    #264454

    Hi,

    In my case I want to delete those data from tables Orders and customers which is not selecting in below join any Help..

    select * from T1580 a inner join T2115 b on a.C301289100 = b.C1000000161 and b.C7 < 5

    Thanks

    Thanks

  • July 23, 2012 at 2:44 am

    #1516107

    I hope the below mentioned query will satisfy your request

    DELETEord

    FROMOrders ord

    LEFT OUTER JOINCustomers c

    ONord.CustomerID = c.CustomerID

    ANDc.FirstName = 'vikash'

    WHEREc.CustomerID IS NULL

    Edit: Edited the incorrect code

    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • July 23, 2012 at 3:04 am

    #1516112

    Kingston Dhasian (7/23/2012)

    I hope the below mentioned query will satisfy your request

    DELETEord

    FROMOrders ord

    LEFT OUTER JOINCustomers c

    ONord.CustomerID = c.CustomerID

    ANDc.FirstName = 'vikash'

    WHEREc.CustomerID IS NULL

    Edit: Edited the incorrect code

    Not sure I understand the IS NULL WHERE clause.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • July 23, 2012 at 3:31 am

    #1516120

    Its giving syntax error,I have changed the query with my actual can u help on that.

    Can we use join in delete statement.

    Thanks

  • July 23, 2012 at 3:36 am

    #1516121

    forsqlserver (7/23/2012)

    Its giving syntax error,I have changed the query with my actual can u help on that.

    Can we use join in delete statement.

    delete a

    from T1580 a

    inner join T2115 b on a.C301289100 = b.C1000000161

    and b.C7 < 5

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • July 23, 2012 at 4:11 am

    #1516126

    Phil Parkin (7/23/2012)

    Kingston Dhasian (7/23/2012)

    I hope the below mentioned query will satisfy your request

    DELETEord

    FROMOrders ord

    LEFT OUTER JOINCustomers c

    ONord.CustomerID = c.CustomerID

    ANDc.FirstName = 'vikash'

    WHEREc.CustomerID IS NULL

    Edit: Edited the incorrect code

    Not sure I understand the IS NULL WHERE clause.

    From the below statement, I thought the OP probably wanted to delete the data which were not being SELECTED by his query

    Hence the LEFT OUTER JOIN and the filtering in the WHERE Clause

    In my case I want to delete those data from tables Orders and customers which is not selecting in below join any Help..

    But now I am confused, and I hope your query is what he wants

    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • July 23, 2012 at 4:37 am

    #1516131

    But now I am confused, and I hope your query is what he wants

    Haha, caveat emptor as always.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • July 23, 2012 at 4:38 am

    #1516132

    Hi Phil and king Thanks..

    delete a

    from T1580 a

    inner join T2115 b on a.C301289100 = b.C1000000161

    and b.C7 < 5

    this query will delete my actual data I want to delete the rest data which is not selecting in above written join.

    Thanks

  • July 23, 2012 at 4:43 am

    #1516133

    forsqlserver (7/23/2012)

    Hi Phil and king Thanks..

    delete a

    from T1580 a

    inner join T2115 b on a.C301289100 = b.C1000000161

    and b.C7 < 5

    this query will delete my actual data I want to delete the rest data which is not selecting in above written join.

    So I was wrong after all!

    What is the primary key on table T1580?

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • July 23, 2012 at 4:49 am

    #1516134

    C1 is the primary key...

    Thanks

  • July 23, 2012 at 5:01 am

    #1516136

    In that case, you can use the LEFT OUTER JOIN, as I had suggested earlier

    DELETEa

    FROMT1580 a

    LEFT OUTER JOINT2115 b ON a.C301289100 = b.C1000000161 AND b.C7 < 5

    WHEREb.C1000000161 IS NULL

    You said you were getting an error, can you post the error message you are getting( if any ) now?

    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • July 23, 2012 at 5:06 am

    #1516137

    Now that error is not coming it was my mistake not yours.

    deletion activity is pending as below yet..

    I want to know one thing ..If I am deleting data using join and I want to drop the data on both the tables but only on the first table this delete is running ,when data will be delete on other table then the matchd data is removed from one table.

    How to delete the matched data from second table also?

    Thanks

  • July 23, 2012 at 5:06 am

    #1516138

    Something like this (untested)?

    ;with Remove

    as (

    select a.c1

    from t1580 a

    )

    except

    (

    select a.c1

    from T1580 a

    inner join T2115 b on a.C301289100 = b.C1000000161

    and b.C7 < 5

    )

    delete a

    from t1580 a

    join Remove on a.c1 = Remove.c1

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • July 23, 2012 at 5:31 am

    #1516145

    It is giving error:

    Msg 156,state 1,Line 6

    Incorrect syntax near the keyword except.

    Thanks

  • July 23, 2012 at 5:31 am

    #1516146

    forsqlserver (7/23/2012)

    I want to know one thing ..If I am deleting data using join and I want to drop the data on both the tables but only on the first table this delete is running ,when data will be delete on other table then the matchd data is removed from one table.

    How to delete the matched data from second table also?

    You will have to write a separate query to delete the data from the second table as well

    You can store the results of your SELECT query in some temporary table before deleting from first table

    Then you can use the data in the temporary table to delete rows from the second table

    If you provide some an example with some sample data, DDL of the tables involved and the expected results, we can provide you a script to demonstrate the solution

    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

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

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