我有一个包含数百个表的MySQL数据库 .
我需要根据名为“companyId”的公共列删除所有表中的记录 - 此列存在于数据库的所有表中 .
如何删除所有表中columnId = somenumber的所有记录?
先感谢您 !
伊万诺
@SocratesG我尝试了你的存储过程,但是我在下面创建了SP:
错误1064:您的SQL语法有错误;检查与MySQL服务器版本对应的手册,以便在'@TableName VARCHAR(128)附近使用正确的语法;
DECLARE @MyColumn VARCHAR(128);
在第4行设置@MyColumn ='compan'
SQL语句:
CREATE PROCEDURE deletePortalInstance ()
开始
DECLARE @TableName VARCHAR(128);
DECLARE @MyColumn VARCHAR(128);
SET @MyColumn ='companyId'
宣布MyCursor CURSOR
(选择OBJECT_NAME(c.id)作为ObjectName
FROM dbo.syscolumns c
WHERE
OBJECTPROPERTY(c.id,'ISTABLE') = 1 --Search for tables only
AND c.name = @MyColumn)
打开MyCursor
FETCH NEXT从MyCursor进入@TableName
WHILE @@ FETCH_STATUS = 0
开始
EXEC
(
'DELETE ' + @MyColumn
+' FROM ' + @TableName
+' WHERE ' + @MyColumn + ' = 906454'
)
FETCH NEXT FROM MyCursor into @TableName
结束
关闭MyCursor
DEALLOCATE MyCursor
结束
错误1064:您的SQL语法有错误;检查与MySQL服务器版本对应的手册,以便在'@TableName VARCHAR(128)附近使用正确的语法;
DECLARE @MyColumn VARCHAR(128);
在第4行设置@MyColumn ='compan'
SQL语句:
CREATE PROCEDURE deletePortalInstance ()
开始
DECLARE @TableName VARCHAR(128);
DECLARE @MyColumn VARCHAR(128);
SET @MyColumn ='companyId'
宣布MyCursor CURSOR
(选择OBJECT_NAME(c.id)作为ObjectName
FROM dbo.syscolumns c
WHERE
OBJECTPROPERTY(c.id,'ISTABLE') = 1 --Search for tables only
AND c.name = @MyColumn)
打开MyCursor
FETCH NEXT从MyCursor进入@TableName
WHILE @@ FETCH_STATUS = 0
开始
EXEC
(
'DELETE ' + @MyColumn
+' FROM ' + @TableName
+' WHERE ' + @MyColumn + ' = 906454'
)
FETCH NEXT FROM MyCursor into @TableName
结束
关闭MyCursor
DEALLOCATE MyCursor
结束