June 8, 2002 at 5:02 pm
Yes, I AM a noob.
How do you delete rows from multiple tables? I have three tables and I want to delete all expired records. Here's a part of the DB: (tmpID is the primary key)
tmpUser
tmpID
Username
tmpInfo
tmpID
password
age
gender
expires
tmpKey
tmpID
key
I've tried to use aliases, inner joins, unios and cursors. But can't get it right. I searched the net for "deleting from multiple tables" and found something called triggers. But I still don't get it. Please, help me out.
/Tomi
Ps. This has nothing to do with this topic but anyway: Do server hosts usually allow DMO and the SQLServerAgent?
June 8, 2002 at 5:55 pm
You can do it easily in SQL2K by declaring the foreign key relationships and enabling cascading deletes. In earlier versions (or even SQL2K if you prefer to do it manually) you can do it a trigger if you want it done automatically, or just in a stored proc or even client code if you only want to do it in certain cases. A simple trigger would like this, assuming that tmpuser is the "main" table:
create trigger d_tmpUser on tmpuser for delete
as
set nocount on
delete from tmpinfo where tmpid in (select tmpid from deleted)
delete from tmpKey where tmpid in (select tmpid) from deleted)
Andy
June 8, 2002 at 7:46 pm
thanks Andy!
Hmm... I think a stored procedure would do better for me. But I don't know how to make one because expires is in the tmpInfo table. So how do I delete the others? INNER JOIN doesn't work. How do you do it with a cursor? Oh, yes and I'm running MSSQL 7.
Something like this?
set nocount on
DECLARE expired_cursor CURSOR FOR
SELECT tmpID from tmpInfo
WHERE expires < GETDATE()
OPEN expired_cursor
FETCH NEXT FROM expired_cursor
... what do I put here?
CLOSE expired_cursor
DEALLOCATE expired_cursor
Thanks again!
/Tomi
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply