运行 DELETE 语句存储在表中的某些表名 [英] Run a DELETE statement certain table names stored in a table

问题描述

我有一个表，用于存储某些表的名称 - tableNames.我想对其中一些表运行 DELETE 语句(从它们代表的表中删除所有行，而不是从 tableNames 中删除它们).我以为我可以做

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

但我不断收到不正确的语法错误.我还考虑过在 WHILE 循环中遍历表并使用变量进行存储，但我希望有更简单的方法.具体来说，这是针对 Microsoft SQL

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

推荐答案

您不能那样做，因为内部 SELECT 只是您要从中删除的另一个集合.

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

基本上，您正在创建一个表名表并告诉数据库将其删除.如果没有动态 sql 和 EXEC

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]

您的输出将如下所示:

DELETE myTableName1
DELETE myTableName2
DELETE myTableName3

然后简单地将这个查询的结果复制到窗口外并运行它们.

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

如果您需要在 SQL 中自动执行此操作，您可以连接结果中的所有输出字符串，并将它们作为参数发送给 EXEC 调用.

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.

这篇关于运行 DELETE 语句存储在表中的某些表名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！