0

Hello I need to be able to search for a record that is a year old and then delete it. I have this script which allows me to delete the record from one table, based on a date given by another table,however I need to add code to this so that I am able to delete a record from a different table relating to CardID. The table that I need to delete from is table11 and Primary key is CardID.

I think I need a left join, but I am not to sure on how to go about it.

DECLARE @deleted TABLE (Card INT)

INSERT INTO @deleted
SELECT Card FROM table9
WHERE recordstatus = 4


DELETE table9
FROM @deleted d, table51

WHERE table51.ActionString LIKE '%' + CAST(d.card AS VARCHAR(20))+ '%'
AND table51.AuditDate <= (SELECT CONVERT(VARCHAR(8),today,112) FROM(SELECT DATEADD(YEAR,-1,GETDATE()) AS today)aa)
AND table09.Card = d.card

Thanks in advance, Hope you can help.

Leigh
  • 133
  • 1
  • 10
  • DO NOT EVER USE that implied syntax again. it is is a horrible syntax (Accidental cross joins, hard to maintain, etc.) that has been outdated for almost 20 years, learn to write explicit joins. – HLGEM Mar 15 '11 at 14:21
  • I think whoever downvoted is harsh. His syntax is bad, but that's why he's here asking for advice. +1 – Matthew Mar 15 '11 at 14:57
  • thank you so much, I am only trying to learn, just need to know how to delete the record in the next table , thanks matthew – Leigh Mar 15 '11 at 15:11

1 Answers1

0

same as this question

edit: as @HLGEM mentioned, the WHERE clause goes where you expect it to go, after the join.

Community
  • 1
  • 1
vlad
  • 4,748
  • 2
  • 30
  • 36
  • thank you, I did see that, however where would I add in my Where statments to get the results I need – Leigh Mar 15 '11 at 14:18
  • @leigh, the where clause goes the same place the where clause always goes, after the joins and before the group by if you have one of those. In the link, pay attention to the transaction part, it is important. – HLGEM Mar 15 '11 at 14:23