Delete data

[ashab02]

Hello

I was wondering if some could help me. I would like to delete data from a series of tables out of my database based on the same criteria "where assigned_date > '10/07/2001'".

My question is how do I automate this by using sql from sql technique?

shab

 

[hmckillop]

Try something like this

declare @sSQL varchar(2000)
declare @table_name varchar(255)

DECLARE c1 CURSOR FOR 
SELECT table_name from information_schema.tables
where table_name in ('Table1', 'Table2', 'Table3') --replace with all tables you want
order by table_name ASC

OPEN c1

FETCH NEXT FROM c1 
INTO @table_name

WHILE @@FETCH_STATUS = 0
BEGIN
	SET @sSQL = 'DELETE ' + @table_name + ' WHERE assigned_date > ''10/07/2001'''
	SELECT @sSQL
	
   -- Get the next author.
   FETCH NEXT FROM c1
   INTO @table_name
END

CLOSE c1
DEALLOCATE c1
GO

You obviously would need to replace the table names from the list.

"I'm living so far beyond my income that we may almost be said to be living apart