Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2003
    Posts
    5

    Unanswered: delete from multiple table

    I've read several topic on this forum on how to delete records from multiple table in only one query but found nothing really definitive.
    The point seems to be that that isn't really possible. Transact-SQL has a syntax like "delete from table1 from table2 where fieldOfBothTable1and2='value'" but in some topic around it says that it doesn't delete all rows in both the table....
    What I need is a query that, given N tables (not necessairly linked in a relationship) with a field in common, delete all the rows in every table "where" that field is equal to "something".
    Is there any query like this?
    Thank you in advance...

  2. #2
    Join Date
    Jan 2003
    Location
    India
    Posts
    523
    delete from table1 from table2 where

    this deletes the records only from the first table matching the criteria used in the where claues for the second table.
    If the tables have relations set use cascade deletes or go for an stored procedure.

  3. #3
    Join Date
    Jan 2003
    Location
    India
    Posts
    523
    delete from tableA,tableB
    where tableA.id=tableB.id

  4. #4
    Join Date
    Sep 2003
    Posts
    5
    Originally posted by harshal_in
    delete from tableA,tableB
    where tableA.id=tableB.id
    That isn't ms SQL server correct syntax, isn't it?
    I've already tried that, but it give me syntax error in proximity of ","

  5. #5
    Join Date
    Jan 2003
    Location
    India
    Posts
    523
    Originally posted by dtssagio
    That isn't ms SQL server correct syntax, isn't it?
    I've already tried that, but it give me syntax error in proximity of ","

    infact it is .
    search BOL for : DELETE, removing data from tables

  6. #6
    Join Date
    Sep 2003
    Posts
    5

    Red face

    Originally posted by harshal_in
    infact it is .
    search BOL for : DELETE, removing data from tables
    I've searched but found nothing of any interest...

  7. #7
    Join Date
    Jan 2003
    Location
    India
    Posts
    523
    Originally posted by dtssagio
    I've searched but found nothing of any interest...


    The DELETE statement removes one or more rows in a table or view. A simplified form of the DELETE syntax is:

    DELETE table_or_view FROM table_sources WHERE search_condition

    table_or_view names a table or view from which the rows are to be deleted. All rows in table_or_view that meet the qualifications of the WHERE search condition are deleted. If a WHERE clause is not specified, all the rows in table_or_view are deleted. The FROM clause specifies additional tables or views and join conditions that can be used by the predicates in the WHERE clause search condition to qualify the rows to be deleted from table_or_view. Rows are not deleted from the tables named in the FROM clause, only from the table named in table_or_view.

    Any table that has all rows removed remains in the database. The DELETE statement deletes only rows from the table; the table must be removed from the database by using the DROP TABLE statement.

    To delete rows using DELETE

    Transact-SQL

    This script shows the three DELETE statements needed to delete the rows associated with products supplied by the company named Lyngbysild in the Northwind database. This would not be a typical business operation because it involves deleting lines from existing orders, but it does show a series of deletes of differing complexity.

    USE Northwind
    GO
    DELETE [Order Details]
    FROM Suppliers, Products
    WHERE Products.SupplierID = Suppliers.SupplierID
    AND Suppliers.CompanyName = 'Lyngbysild'
    AND [Order Details].ProductID = Products.ProductID
    GO
    DELETE Products
    FROM Suppliers
    WHERE Products.SupplierID = Suppliers.SupplierID
    AND Suppliers.CompanyName = 'Lyngbysild'
    GO
    DELETE Suppliers
    WHERE CompanyName = 'Lyngbysild'
    GO


    See Also

    DROP TABLE

  8. #8
    Join Date
    Sep 2003
    Posts
    5
    Originally posted by harshal_in
    The DELETE statement removes one or more rows in a table or view. A simplified form of the DELETE syntax is:

    DELETE table_or_view FROM table_sources WHERE search_condition

    table_or_view names a table or view from which the rows are to be deleted. All rows in table_or_view that meet the qualifications of the WHERE search condition are deleted. If a WHERE clause is not specified, all the rows in table_or_view are deleted. The FROM clause specifies additional tables or views and join conditions that can be used by the predicates in the WHERE clause search condition to qualify the rows to be deleted from table_or_view. Rows are not deleted from the tables named in the FROM clause, only from the table named in table_or_view.

    Any table that has all rows removed remains in the database. The DELETE statement deletes only rows from the table; the table must be removed from the database by using the DROP TABLE statement.

    To delete rows using DELETE

    Transact-SQL

    This script shows the three DELETE statements needed to delete the rows associated with products supplied by the company named Lyngbysild in the Northwind database. This would not be a typical business operation because it involves deleting lines from existing orders, but it does show a series of deletes of differing complexity.

    USE Northwind
    GO
    DELETE [Order Details]
    FROM Suppliers, Products
    WHERE Products.SupplierID = Suppliers.SupplierID
    AND Suppliers.CompanyName = 'Lyngbysild'
    AND [Order Details].ProductID = Products.ProductID
    GO
    DELETE Products
    FROM Suppliers
    WHERE Products.SupplierID = Suppliers.SupplierID
    AND Suppliers.CompanyName = 'Lyngbysild'
    GO
    DELETE Suppliers
    WHERE CompanyName = 'Lyngbysild'
    GO


    See Also

    DROP TABLE

    thanks a lot...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •