Post reply

Can you delete from multiple tables with a Delete Stmt?

  • March 23, 2006 at 8:37 am

    #114032

    Can you have a Delete Statement which joins multiple tables and delete records from each of these tables based on a Where Criteria? We have SQL Server 2000 (SP3) running on Windows 2000.

    Thanks in advance, Kevin

  • March 23, 2006 at 8:46 am

    #628370

    No.

    You can only write to one table at a time.

    (insert, update or delete)

    It's ok to do ops based on joins from several tables, but the columns that are to be changed must all reference the same single table.

    /Kenneth

  • March 23, 2006 at 9:22 pm

    #628505

    You can create a view from those table and INSTEAD OF DELETE trigger on it.

    It gonna be several delete statements inside of this trigger, but than you can execute all those DELETEs in right order in one run:

    DELETE FROM <ViewName>

    _____________
    Code for TallyGenerator

  • March 24, 2006 at 3:51 am

    #628541

    If it's a parent table and several child tables you can create foreign keys (or modify existing ones) and set them to cascade delete. Then you delete from the parent and the deletes cascade down to the child tables.

    This can give performance problems, especially with big tables and multiple cascades so if you go this route, watch your query times.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 4 posts - 1 through 3 (of 3 total)

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