How to delete two records from two tables?
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 CONVER开发者_运维百科T(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.
same as this question
edit: as @HLGEM mentioned, the WHERE
clause goes where you expect it to go, after the join.
精彩评论