Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Posts
    38

    Unanswered: selecting tables that meet certain condition

    Hi there,

    I have the following script that selects tables from my database with the same column name and then I delete data that falls within a specified condition. However what I need to be able to do is just select these tables that meet the condition and then just delete the data because at the moment it's also returning tables that I don't need.

    So I just want to use a cursor on a table list that meet the criteria:

    1) have qid column name
    2) qid >= 5000000 and qid < 1500000000 '


    Example

    declare @strqry varchar(1000)

    declare dailyYear cursor
    for SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE 'qid' = COLUMN_NAME order by table_name asc
    open dailyYear
    fetch next from dailyYear into @DelTable

    while @@FETCH_STATUS = 0

    begin

    Set @strqry = 'Delete from '+@DelTable+' where qid >= 5000000 and qid < 1500000000 '

    exec(@strqry)

    fetch next from dailyYear into @DelTable

    end

    close dailyYear
    deallocate dailyYear

    Any help would be greatly appreciated!!

    Thanks

    S

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    ummm...I'm gonna regret this....

    Why what do you get?

    And What are you doing?

    Sounds dangerous...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jan 2004
    Posts
    38
    It's only a test DB not live so no problems if I mess it up. I need to delete a certain subset of data from all tables that meet the previous condition. However, because I'm searching with a column name it's also returning some tables I don't need. This isn't a major problem because when the delete command is run these tables I don't need won't match the criteria so no rows will be deleted.

    condtition : qid >= 5000000 and qid < 1500000000 '

    so really I only want tables returned that have a qid meeting this condition??

    Thanks

    S

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I was thinking of something like

    Code:
    SELECT 'SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.Columns c WHERE COLUMN_NAME = ''OrderId'''
    	+ ' AND EXISTS (SELECT COUNT(*) FROM [' 
    	+ TABLE_NAME + '] WHERE c.TABLE_NAME = ' 
    	+ TABLE_NAME + ' AND OrderId >  10250 AND Order < 10260'
      FROM INFORMATION_SCHEMA.Tables 
     WHERE TABLE_NAME IN (SELECT DISTINCT TABLE_NAME 
    			FROM INFORMATION_SCHEMA.Columns 
    		       WHERE COLUMN_NAME = 'OrderId')
    But I think I'm barking up the wrong tree
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •