Post reply

Delete rows using join query

  • September 18, 2008 at 12:18 am

    #190859

    How to Delete rows from multiple tables using join query:)

  • September 18, 2008 at 5:42 am

    #873518

    You can't delete multiple tables at once. Each table will get deleted individually. However, you can use a select statement and joins to determine the data to be deleted. This example is straight from BOL:

    DELETE FROM Sales.SalesPersonQuotaHistory

    FROM Sales.SalesPersonQuotaHistory AS spqh

    INNER JOIN Sales.SalesPerson AS sp

    ON spqh.SalesPersonID = sp.SalesPersonID

    WHERE sp.SalesYTD > 2500000.00;

    The exception, sort of, to this occurs when you have referential constraints set up with cascading deletes enabled. This allows you to delete a row in the parent table of a chain of tables and all the child tables associated rows will be deleted automatically.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply