SQL - 根据列值删除所有表中的行

4 回答

• 0

  DELETE FROM table_Name where columnn_name = value ;
  

  例

  DELETE fom students where rll_num = 5 ;
  

  回复于 2024-04-16T19:20:32+08:00
• 0

  DELETE FROM <tblName> where columnId = <Your Column Id val>
  

  但我认为您使用外键使用参照完整性约束并使用 On Delete Cascade

  根据您的新信息，

  Update :

  DELETE t1, t2
        FROM <table1> t1
        JOIN <table2> t2 ON t1.columnId = t2.columnId 
       WHERE t1.columnId  = <Your Column Value>
  

  说明：

  • 这里我们要删除多个表中的记录，所以我们使用 DELETE FROM table1,table2 等

  • 由于所有表中都有一个具有相同名称的列，因此我们生成了 JOIN ，因此可以一次删除100个表中的所有行 .

  3.最后我们有 WHERE t1.columnId = 1 之类的条件，表示在table1中，检查columnId = 1，所以当我们加入时，它选择100表中的所有这些行，然后删除它 .

  回复于 2024-04-16T19:20:32+08:00
• -1

  你可以试试这个

  DECLARE @TableName VARCHAR(128);
  DECLARE @MyColumn VARCHAR(128);
  SET @MyColumn = 'MyColumnName'
  DECLARE MyCursor CURSOR FOR  
  (SELECT OBJECT_NAME(c.id) as ObjectName
                FROM dbo.syscolumns c
                WHERE 
                  OBJECTPROPERTY(c.id,'ISTABLE') = 1 --Search for tables only
                  AND c.name = @MyColumn)
  
  OPEN MyCursor 
  FETCH NEXT FROM MyCursor into @TableName
  WHILE @@FETCH_STATUS = 0  
  BEGIN
      EXEC
              (
                   'DELETE ' + @MyColumn
                  +' FROM ' + @TableName  
                  +' WHERE ' + @MyColumn + ' <= 1000'
              )
      FETCH NEXT FROM MyCursor into @TableName
  END
  CLOSE MyCursor   
  DEALLOCATE MyCursor
  

  回复于 2024-04-16T19:20:32+08:00
• -1

  DELETE FROM companyId where columnId = 1
  

  回复于 2024-04-16T19:20:32+08:00