none
How to delete rows from multiple tables in SQL

    Question

  • I have an SQL 2000 server. I have multiple tables in the db that have a row with a time stamp of '10-23-2007'. What I am trying to do is delete these specific rows because they don't belong.

    So I need to query the db for table names that are like 'elect_Sub%' and then execute a query on those tables that would delete the row with the time_stamp '10-23-2007'. I know that I have to use the db schema to get the table names, but I need help in writing the sql script that will automatically scroll through the tables.

     

    Thanks, 

    Tuesday, October 23, 2007 6:33 PM

Answers

  • Try this...

     

    Code Block

    USE [db_name]

    GO

     

    DECLARE @table_name varchar(50),

    @SQL varchar(100)

     

    DECLARE curDelete INSENSITIVE SCROLL CURSOR

    FOR

    SELECT name

    FROM sys.objects

    WHERE type = 'u'

    AND name LIKE 'elect_Sub%'

     

    OPEN curDelete

     

    FETCH NEXT FROM curDelete INTO @table_name

     

    WHILE @@FETCH_STATUS = 0

    BEGIN

    SET @SQL = 'DELETE FROM ' + @table_name + ' WHERE time_stamp >= ' + CHAR(39) + '10-23-2007' + CHAR(39) +

    ' AND time_stamp < ' + CHAR(39) + '10-24-2007' + CHAR(39)

    EXEC (@SQL)

     

    FETCH NEXT FROM curDelete INTO @table_name

    END

     

    CLOSE curDelete

    DEALLOCATE curDelete

     

     

     

    Tuesday, October 23, 2007 6:47 PM
  •  

    Code Block

    select 'delete '+table_name+' where time_stamp = ''10-23-2007'''

    from information_schema.tables

    where table_name like 'elect_Sub%'

     

     

    Execute the results to achieve the deletion.

    Change the time_stamp column condition as you want. ( =/>/>=).

     

    Thanks

    Naras.

     

     

    Tuesday, October 23, 2007 9:44 PM

All replies

  • Try this...

     

    Code Block

    USE [db_name]

    GO

     

    DECLARE @table_name varchar(50),

    @SQL varchar(100)

     

    DECLARE curDelete INSENSITIVE SCROLL CURSOR

    FOR

    SELECT name

    FROM sys.objects

    WHERE type = 'u'

    AND name LIKE 'elect_Sub%'

     

    OPEN curDelete

     

    FETCH NEXT FROM curDelete INTO @table_name

     

    WHILE @@FETCH_STATUS = 0

    BEGIN

    SET @SQL = 'DELETE FROM ' + @table_name + ' WHERE time_stamp >= ' + CHAR(39) + '10-23-2007' + CHAR(39) +

    ' AND time_stamp < ' + CHAR(39) + '10-24-2007' + CHAR(39)

    EXEC (@SQL)

     

    FETCH NEXT FROM curDelete INTO @table_name

    END

     

    CLOSE curDelete

    DEALLOCATE curDelete

     

     

     

    Tuesday, October 23, 2007 6:47 PM
  •  

    Code Block

    select 'delete '+table_name+' where time_stamp = ''10-23-2007'''

    from information_schema.tables

    where table_name like 'elect_Sub%'

     

     

    Execute the results to achieve the deletion.

    Change the time_stamp column condition as you want. ( =/>/>=).

     

    Thanks

    Naras.

     

     

    Tuesday, October 23, 2007 9:44 PM
  • This forum is great, both methods work. This code by Naras, considering my limited experience with SQL, I can understand better. I appreciate the help from both of you.

    Wednesday, October 24, 2007 1:59 PM
  • Thanks TechZone.

    Please check the status as 'Answered'.

    Naras.

     

    Wednesday, October 24, 2007 2:34 PM