Don't like ads? PRO users don't see any ads ;-)
Public Pastes
Guest

Untitled

By: a guest on Jun 30th, 2012  |  syntax: None  |  size: 0.84 KB  |  hits: 10  |  expires: Never
download  |  raw  |  embed  |  report abuse  |  print
Text below is selected. Please press Ctrl+C to copy to your clipboard. (⌘+C on Mac)
  1. How can I delete one record form two tables?
  2. delete from Table1
  3. where recordstatus = 2
  4. and expiry <=(Select convert(varchar(8),today,112)
  5. from(Select dateadd(year,-1,getdate())as today)aa)
  6.        
  7. DECLARE @deleted TABLE (CardID int);
  8.  
  9. delete from Table1
  10. OUTPUT deleted.CardID INTO @deleted (CardID)
  11. where recordstatus = 2
  12. and expiry <=(Select convert(varchar(8),today,112)
  13. from(Select dateadd(year,-1,getdate())as today)aa);
  14.  
  15. DELETE FROM Table2
  16. FROM @deleted d
  17. WHERE Table2.CardID = d.CardID;
  18.        
  19. DELETE FROM Table2
  20. WHERE  CardID IN (SELECT CardID from Table1
  21.     where recordstatus = 2 and expiry <=
  22.     (Select convert(varchar(8),today, 112)
  23.     from(Select dateadd(year,-1,getdate()) as today) aa ));
  24.  
  25. delete from Table1 where recordstatus = 2
  26. and expiry <=(Select convert(varchar(8),today, 112)
  27.     from(Select dateadd(year,-1,getdate()) as today) aa );
create a new version of this paste RAW Paste Data