Delete two tables from a variable table in SQL

I want to be able to use a varable table to delete records from two different tables.

I have this so far

declare @deleted TABLE (Card int)

insert into @deleted
select Card from table1
where recordstatus = 4

delete from table2
from @deleted d
where table2.actionstring like d.card
and Select convert(varchar(8),today,112)
from(Select dateadd(year,-1,getdate())as today)aa

sorry if this is confusing, I am using sql managment 2005 I basically want to be able to get a card number from table one, check to see if the date in table 2 is greater than a year if so delete the record in table 1.

In table two I do not have a field for card number so I need a LIKE statement.

I only have a error in the second part which reads,- 开发者_如何学编程 from @deleted d

Thank you in advance

Your second part of the statement is quite confusing indeed.... you're mixing the DELETE with SELECT in a WHERE clause..... what exactly are you trying to achieve here??

declare @deleted TABLE (Card int)

insert into @deleted
  select Card from table1
  where recordstatus = 4

delete table2
from @deleted d
where table2.actionstring LIKE '%' + CAST(d.card AS VARCHAR(20)) + '%'
and CONVERT(DATETIME, table2.Date, 112) <= DATEADD(YEAR, -1, GETDATE())

delete table1
from @deleted d
where table1.card = d.card
and CONVERT(DATETIME, table1.Date, 112) <= DATEADD(YEAR, -1, GETDATE())

So are you trying to delete all rows from table2 where the the Table2.ActionString is equal to the Card column in your @deleted table?? Not quite clear.

Also : what's with the date constraint?? What field in table2 do you want to check against?? What is the condition - <= or >= or what?

DELETE table1
FROM table1 INNER JOIN 
     table2 ON table1.card = table2.actionstring
WHERE table1.recordstatus = 4 AND
      table2.SomeDateColumn >= DATEADD(year, -1, GETDATE())

Try something like:

delete t1
from
   table1 t1
   join table2 t2
      on t2.actionstring like t1.card + '%'
      and [... other criteria ...]

The fact that table2 doesn't have a key complicates things a bit. In particular I cannot see how to avoid repeating the search in table2 in this case.

Anyway, here goes:

DECLARE @deleted TABLE (card int);

/* store cards that are actually going to be deleted */
INSERT INTO @deleted (card)
SELECT DISTINCT t1.card
FROM table1 t1
  INNER JOIN table2 t2 ON t2.Date <= DATEADD(year, -1, GETDATE())
    AND t2.actionstring LIKE '%' + RIGHT(100000000 + t1.card, 8) + '%'
WHERE t1.recordstatus = 4

/* delete the history for the selected cards */
DELETE FROM t2
FROM @deleted t1
  INNER JOIN table2 t2 ON t2.Date <= DATEADD(year, -1, GETDATE())
    AND t2.actionstring LIKE '%' + RIGHT(100000000 + t1.card, 8) + '%'

/* delete the cards from table1 */
DELETE FROM table1
FROM @deleted d
WHERE table1.card = d.card
  AND table1.recordstatus = 4  /* not sure if this condition is needed again
                                  but it will certainly do no harm */