How can I delete one record form two tables?

I was just wondering if I could be able to delete a record from two differnt tables.

I think I need a lookup statement but I am unsure on where to begin.

In both tables I have a field called CardID.

In my first table I have a st开发者_如何学JAVAatement which reads,

delete from Table1
where recordstatus = 2
and expiry <=(Select convert(varchar(8),today,112)
from(Select dateadd(year,-1,getdate())as today)aa)

then I need to delete the record from table 2.

Any help will be deeply apprecitated

Thanks in advance.

DECLARE @deleted TABLE (CardID int);

delete from Table1
OUTPUT deleted.CardID INTO @deleted (CardID)
where recordstatus = 2
and expiry <=(Select convert(varchar(8),today,112)
from(Select dateadd(year,-1,getdate())as today)aa);

DELETE FROM Table2
FROM @deleted d
WHERE Table2.CardID = d.CardID;

When deleting from Table1, you also store the deleted CardID values into a table variable (the added OUTPUT clause). Then you use that list to delete from Table2 accordingly.

If you always need to delete from the two tables synchronously, a trigger for delete on Table1 would fit here better, I think.

Is there a reason why you don't want to issue two different queries ? This would be the easiest solution. I guess what's preventing you from doing so is that you might not have the correct primary/foreign keys structure in place on your database.

If you really want to use only one query, you might want to look into triggers and/or cascade updates

Try this

DELETE FROM Table2 
WHERE  CardID IN (SELECT CardID from Table1 
    where recordstatus = 2 and expiry <=
    (Select convert(varchar(8),today, 112) 
    from(Select dateadd(year,-1,getdate()) as today) aa ));

delete from Table1 where recordstatus = 2 
and expiry <=(Select convert(varchar(8),today, 112)
    from(Select dateadd(year,-1,getdate()) as today) aa );

DELETE t2 from table2 as t2 join table1 as t1 on t1.CardID=t2.CardID where t1.recordstatus = 2 and t1.expiry <=(Select convert(varchar(8),today,112) from(Select dateadd(year,-1,getdate())as today)aa)

this will be your first delete query to delete records from the table2 and then use your original query to delete records from table1... and in this query if i missed any field then add t1. before that like i added t1.expiry