SQL query+SQL Server

mssql_v2005
mssql_v2005 used Ask the Experts™
on
I want to delete rows from 3 tables based on ID column. Can you please share logic/or query to delete rows.

ex: 3 tables with the name of A,B,C

ID column exist on all 3 tables. I will pass ID as input parameter and want to delete rows from 3 tables from single query.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Developer
Top Expert 2015
Commented:
Hi,

You can create a stored procedure as below and try.

CREATE PROCEDURE sp_DeleteData(@Id int)
AS

BEGIN 
   DELETE * FROM A WHERE Id=@Id;
   DELETE * FROM B WHERE Id=@Id;
   DELETE * FROM C WHERE Id=@Id;
END

Open in new window

Author

Commented:
I don;t want SP, can you share without SP
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
The DELETE command only accepts a table or a view name so I don't think you can't do it from a single statement.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

If you want to achieve with your single query then you need to CREATE FOREIGN KEY with ON DELETE CASCADE

Best Regards

Author

Commented:
This solution is working partially for me.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
I think you were too fast to accept a solution.
Are the tables related? A delete trigger is an acceptable solution for you?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial