i would like to use two tables in my deleting stored procedure. Normally, i can write the query like this:
delete * from table_1 x, table_2 y
where x.ID = y.ID
But i coundn't do it with stored procedure(as you know=)
please help..
-
sorry, i should say.. Microsoft SQL Server 2008 -- Expressjakobiyem– jakobiyem2010-12-19 00:10:43 +00:00Commented Dec 19, 2010 at 0:10
-
2stackoverflow.com/questions/783726/…Sebastian Piu– Sebastian Piu2010-12-19 00:30:51 +00:00Commented Dec 19, 2010 at 0:30
Add a comment
|
4 Answers
DELETE FROM table_1
FROM table_1
INNER JOIN table_2 AS Y
ON table_1.id = Y.id
1 Comment
Will Marcouiller
This will only delete rows from table_1. The OP wishes to delete all rows from both tables with only one delete statement, which after multiple tries, I think he'd better go with the link in Sebastian Piu's comment.
Thank you for all answers, i could do it with using "renegm" and "Sebastian Piu"s comments. Yes, i'd like to delete all rows from both tables so i did it like that:
USE DB
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE delUser
(
@ID int
)
AS
DELETE FROM Table_1
FROM Table_1
INNER JOIN Table_2 AS Y
ON Table_1.ID = Y.ID
AND Y.ID = @ID
DELETE FROM Table_2
WHERE Table_2.ID = @ID
It's working but is it really solution i don't know exactly =))
Comments
select distinct ID
into #temp
from table_1
join table_2
on table_1.ID = table_2.ID
delete table_1
where ID in (select * from #temp)
delete table_2
where ID in (select * from #temp)
drop table #temp
simplest + no confusion
Comments
Delete only is for a table.
MSDN description for delete
DELETE (Transact-SQL)
Removes rows from a table or view.
if u want always delete from table2 when delete from table1, u can use trigger on table1.
see this link: http://forums.devshed.com/showpost.php?p=322160&postcount=5
