delete records from table2 that are not in table1

• Krasavita

  SSC-Insane

  Points: 23138

  More actions

  March 17, 2011 at 1:51 pm

  #234322

  Hello, I need to delete records from table2 that are not in table1

  I have a table1 and table2

  table1

  DB, CODE, RATE, MAX(CONV_DATE) AS MaxConv_Date

  FROM table1

  table2

  DB, CODE, RATE, Date

  FROM

  table2

  I need to delete records from table2 that are not in table1

  Thank you

• Ron McCullough

  SSC Guru

  Points: 63877

  More actions

  March 17, 2011 at 2:38 pm

  #1300224

  To properly assist you with tested T-SQL please post table definition(s), sample data and desired results.

  To do so please click on the first link in my signature block for a method (including T-SQL statements) to assist us to assist you.

  If everything seems to be going well, you have obviously overlooked something.

  Ron
  
  Please help us, help you -before posting a question please read[/url]
  Before posting a performance problem please read[/url]

• Evil Kraig F

  SSC Guru

  Points: 100851

  More actions

  March 17, 2011 at 2:44 pm

  #1300231

  Krasavita (3/17/2011)

  ---

  Hello, I need to delete records from table2 that are not in table1

  I have a table1 and table2

  table1

  DB, CODE, RATE, MAX(CONV_DATE) AS MaxConv_Date

  FROM table1

  table2

  DB, CODE, RATE, Date

  FROM

  table2

  I need to delete records from table2 that are not in table1

  Thank you

  As Ron mentioned, this is easier with actual DDL and sample data. But, for a quick question, a quick answer to get you on track. You want what's known as an anti-semi join. Left Join where null for the rest of us hacks.

  Left join the table you're looking to delete from to the table that has the reference, and anytime the reference is missing (null), delete that row.

  ---

  - Craig Farrell

  Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

  For better assistance in answering your questions[/url] | Forum Netiquette
  For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
  Twitter: @AnyWayDBA

• Jeff Moden

  SSC Guru

  Points: 1004372

  More actions

  March 17, 2011 at 10:30 pm

  #1300310

  Krasavita (3/17/2011)

  ---

  Hello, I need to delete records from table2 that are not in table1

  I have a table1 and table2

  table1

  DB, CODE, RATE, MAX(CONV_DATE) AS MaxConv_Date

  FROM table1

  table2

  DB, CODE, RATE, Date

  FROM

  table2

  I need to delete records from table2 that are not in table1

  Thank you

  EXCEPT will do a fine job on this. Have a look at it in Books Online.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)