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

• forsqlserver

  SSCoach

  Points: 18902

  More actions

  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

• Kingston Dhasian

  SSCoach

  Points: 19799

  More actions

  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/

• Phil Parkin

  SSC Guru

  Points: 246960

  More actions

  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
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• forsqlserver

  SSCoach

  Points: 18902

  More actions

  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

• Phil Parkin

  SSC Guru

  Points: 246960

  More actions

  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
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• Kingston Dhasian

  SSCoach

  Points: 19799

  More actions

  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/

• Phil Parkin

  SSC Guru

  Points: 246960

  More actions

  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
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• forsqlserver

  SSCoach

  Points: 18902

  More actions

  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

• Phil Parkin

  SSC Guru

  Points: 246960

  More actions

  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
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• forsqlserver

  SSCoach

  Points: 18902

  More actions

  July 23, 2012 at 4:49 am

  #1516134

  C1 is the primary key...

  Thanks

• Kingston Dhasian

  SSCoach

  Points: 19799

  More actions

  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/

• forsqlserver

  SSCoach

  Points: 18902

  More actions

  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

• Phil Parkin

  SSC Guru

  Points: 246960

  More actions

  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
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• forsqlserver

  SSCoach

  Points: 18902

  More actions

  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

• Kingston Dhasian

  SSCoach

  Points: 19799

  More actions

  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/