DELETE on INNER Join doesn't work

• lof

  Default port

  Points: 1498

  More actions

  July 5, 2002 at 5:32 pm

  #78345

  I have a simple delete procedure like this:

   
  
  DELETE
  
  FROM tSchedule s INNER JOIN tSchDoneTemp d ON s.SDG=d.SDG AND s.Method=d.Method AND s.Matrix<>d.Matrix
  
  

  when trying to compile I get

  Server: Msg 170, Level 15, State 1, Line 2

  Line 2: Incorrect syntax near 's'.

  when it is set up like this:

   
  
  DELETE FROM tSchedule
  
  WHERE SDG IN (Select SDG from tSchDoneTemp) AND Method IN (Select Method from tSchDoneTemp) AND Matrix NOT IN (Select Matrix from tSchDoneTemp)
  
  

  it runs fine.

  BOL says that both should work the same.

  Why is the first one not working, any ideas?

  Thanks,

  Jakub

• K. Brian Kelley

  SSC Guru

  Points: 114642

  More actions

  July 5, 2002 at 10:15 pm

  #432144

  Try this:

  
  
  DELETE tSchedule
  
  FROM tSchedule s 
  
    INNER JOIN tSchDoneTemp d 
  
      ON s.SDG=d.SDG 
  
      AND s.Method=d.Method 
  
      AND s.Matrix<>d.Matrix
  
  

  K. Brian Kelley

  bkelley@sqlservercentral.com

  http://www.sqlservercentral.com/columnists/bkelley/

  K. Brian Kelley
  @kbriankelley