How to delete data from multiple tables at a time

  • can anyone help me how to delete data from multiple tables at a time.

    Example

    category

    cat_id

    name

    orders

    id

    name

    cat_id

    so if i delete cat_id in category table first it should delete the id in orders table then it should delete that id in category table so how can i do this can anyone help me please.

  • Create foreign keys between the tables and have ON DELETE CASCADE set on them.

    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
  • i cantget u what u r saying

  • To delete from one table and have matching rows automatically deleted from other tables, you need to create foreign key constraints between the tables. When you create the foreign key constraint, specify CASCADE for the on delete action.

    All the details for creating foreign keys are in Books Online (press F1 while in Management Studio)

    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
  • while deleting the cat_id from category table if i want to get a message then how to do regarding cat_id is present in order table

  • or try creating triggers

    Pramod
    SQL Server DBA | MCSE SQL Server 2012/2014

    in.linkedin.com/in/pramodsingla/
    http://pramodsingla.wordpress.com/

  • can i have one example regarding that

  • ashalatha.cse76 (6/11/2014)


    can i have one example regarding that

    I would not recommend using triggers for this. Use foreign keys with cascading deletes as Gail suggested.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • ashalatha.cse76 (6/11/2014)


    while deleting the cat_id from category table if i want to get a message then how to do regarding cat_id is present in order table

    Under what exact circumstances do you want a message, what should the message say, and to whom should the message go?

    John

  • If we are deleting that it should display a message there itself.

  • Messages are a client application concern. SQL doesn't really have the ability to send custom notifications to users.

    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 11 posts - 1 through 10 (of 10 total)

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