Run a DELETE statement certain table names stored in a table

I have a table which stores the names of certain tables - tableNames. I'd like to run a DELETE statement on some of those tables (deleting all the rows from the tables they represent, not removing them from tableNames). I thought I could just do

DELETE FROM (SELECT tableName FROM tablesNames WHERE ...) AS deleteTables

But I keep getting an incorrect syntax error. I also thought about iterating through a table in a WHILE loop and storing using a variable, but that I'm hoping there's more simpler way. Specifically, this is for Microsoft SQL

Answers


You cannot do it that way because the inner SELECT is simply another set you're deleting from.

Basically you're creating a table of table names and telling the DB to delete it. Even iterating through them won't work without dynamic sql and EXEC

Do you need to automate this process?

What I've done in the past is something like this

SELECT
    'DELETE ' + tableName
FROM
    tablenames
WHERE
   [conditions]

your output will look like this:

DELETE myTableName1
DELETE myTableName2
DELETE myTableName3

And then simply copying the results of this query out of the window and running them.

IF you need to automate this in SQL you can concatenate all the output strings in the result and send them as a parameter to an EXEC call.


Need Your Help

Generating triangular/hexagonal coordinates (xyz)

math coordinates vector coordinate-systems hexagonal-tiles

I'm trying to come up with an iterative function that generates xyz coordinates for a hexagonal grid. Maths has never been easy for me (I'm just not very clever!) and this problem has me stumped. W...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.