Delete selected?
-
Hi all!
I have a stored procedure as follows:
Alter Procedure usp_msgGetPopup
@memID numeric(9, 0),
@Sender numeric(9, 0)
As
SET NOCOUNT ON
SELECT msgPopup.msgID, Subject, DateTime, Message
FROM msgPopup INNER JOIN msgInfo
ON (msgPopup.msgID = msgInfo.msgID)
INNER JOIN msgMessage
ON (msgInfo.msgID = msgMessage.msgID)
WHERE
Sender = @Sender AND Receiver = @memID
Now, how do I delete the records I just selected
I was more diffucult than I thought!Thanks!
/Tomi
Edited by - tomiz on 07/29/2002 11:12:17 AM
-
If these are in two tables, then you need two delete statements.
To delete the msgPopup,
delete msgpopup
FROM msgPopup INNER JOIN msgInfo
ON (msgPopup.msgID = msgInfo.msgID)
INNER JOIN msgMessage
ON (msgInfo.msgID = msgMessage.msgID)
WHERE
Sender = @Sender AND Receiver = @memID
Steve Jones
-
Thanks Steve!
But I can't get it to work with three DELETE stamements. Because when I use
Sender = @Sender AND Receiver = @memID
the rows in msgPopup have already been deleted. Actually the statement should look like this:
msgInfo.Sender = @Sender AND msgPopup.Receiver = @memID
What should I do??
/Tomi
-
Aha! I see your issue. Sorry
What you need to do is capture the msgID from msgPopup. Then delete the MsgInfo table and finally the msgPopup (or reverse) depending on the parent child order.
I'd also wrap this in a transaciton so you do not orphan rows.
Steve Jones
-
Hello!
So what your saying is that I should use a cursor
Or am I mistaken?/Tomi
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply