Delete rows from multiple tables using a single query SQL Express 2005 with a WHERE condition

Question

This is the query I m using    DELETE TB1    TB2      FROM TB1         INNER JOIN TB2 ON TB1 PersonID   TB2 PersonID    WHERE  TB1 PersonID   2    It s working fine in MS Access but getting error  Incorrect syntax near       in SQL Server Express 2005    How to solve it  Please help

User · Answer

You can use something like the following   DECLARE db cursor CURSOR FOR   SELECT name  FROM master dbo sysdatabases  WHERE name IN   TB2   TB1    -- use these databases  OPEN db cursor    FETCH NEXT FROM db cursor INTO  name      WHILE   FETCH STATUS   0    BEGIN            DELETE FROM  name WHERE PersonID   2          FETCH NEXT FROM db cursor INTO  name    END

User · Answer

As i know  you can t do it in a sentence   But you can build an stored procedure that do the deletes you want in whatever table in a transaction  what is almost the same

User · Answer

This cannot be done in one statement  You will have to use 2 statements  DELETE FROM TB1 WHERE PersonID    2   DELETE FROM TB2 WHERE PersonID    2

User · Answer

I don t think you can delete from multiple tables at once  though I m not certain    It sounds to me  however  that you would be best to achieve this effect with a relationship that cascades deletes   If you did this you would be able to delete the record from one table and the records in the other would be automatically deleted   As an example  say the two tables represent a customer  and the customer s orders   If you setup the relationship to cascade deletes  you could simply delete record in the customer table  and the orders would get deleted automatically   See the MSDN doc on cascading referential integrity constraints

User · Answer

You cannot DELETE from multiple tables with a single expression in SQL 2005 - or any other standard SQL for that matter  Access is the exception here   The best method to get this effect is to specify FOREIGN KEYS between the table with an ON DELETE trigger

User · Answer

CREATE PROCEDURE sp deleteUserDetails      Email varchar 255  AS     declare  tempRegId as int     Delete UserRegistration where Email  Email       set  tempRegId    select Id from UserRegistration where Email    Email      Delete UserProfile where RegID  tempRegId  RETURN 0

User · Answer

Why you don t use a DELETE CASCADE FK

User · Answer

Use this in procedure  declare cu cursor for SELECT  name  FROM sys Tables where  name  like  tbl    declare  table varchar 100  declare  sql nvarchar 1000   OPEN cu   FETCH NEXT FROM cu INTO  table   WHILE   FETCH STATUS   0   BEGIN       set  sql   N delete from      table     EXEC sp executesql  sql     FETCH NEXT FROM cu INTO  table  END    CLOSE cu    DEALLOCATE cu

User · Answer

I use this for cleaning up data in test development databases  You can filter by table name and record count    DECLARE  sqlCommand VARCHAR 3000   DECLARE  tableList TABLE Value NVARCHAR 128    DECLARE  TableName VARCHAR 128   DECLARE  RecordCount INT   -- get a cursor with a list of table names and their record counts DECLARE MyCursor CURSOR FAST FORWARD FOR SELECT t name TableName             i rows Records     FROM sysobjects t           sysindexes i     WHERE            t xtype    U               -- only User tables           AND i id   t id                     AND i indid IN 0  1        -- 0 Heap  1 Clustered Index           AND i rows  lt  10            -- Filter by number of records in the table           AND t name LIKE  Test      -- Filter tables by name  You could also provide a list                                       -- AND t name IN   MyTable1    MyTable2    MyTable3                                         -- or a list of tables to exclude                                       -- AND t name NOT IN   MySpecialTable           OPEN MyCursor   FETCH NEXT FROM MyCursor INTO  TableName   RecordCount   -- for each table name in the cursor  delete all records from that table  WHILE   FETCH STATUS   0     BEGIN         SET  sqlCommand    DELETE FROM      TableName          EXEC   sqlCommand           FETCH NEXT FROM MyCursor INTO  TableName   RecordCount      END   CLOSE MyCursor  DEALLOCATE MyCursor    Reference info    sysobjects sysindexes SQL Server Cursors

User · Answer

Specify foreign key for the details tables which references to the primary key of master and set Delete rule   Cascade    Now when u delete a record from the master table all other details table record based on the deleting rows primary key value  will be deleted automatically   So in that case a single delete query of master table can delete master tables data as well as child tables data

User · Answer

Try this query  DELETE TB1  TB2 FROM TB1 INNER JOIN TB2   WHERE TB1 PersonID   TB2 PersonID and TB1 PersonID    2

User · Answer

qry    DELETE lg   l  FROM lessons game lg RIGHT JOIN lessons l ON lg lesson id   l id WHERE l id        lessons is Main table and lessons game is subtable so Right Join

User · Answer

DELETE TB1  TB2     FROM customer details         LEFT JOIN customer booking on TB1 cust id   TB2 fk cust id     WHERE TB1 cust id    id

[sql] Delete rows from multiple tables using a single query (SQL Express 2005) with a WHERE condition

Examples related to sql

Examples related to sql-server

Examples related to sql-server-express

Examples related to sql-server-2005-express