Having problems writing a delete statement. Here is what I have so far and it doesn't work. I'm assuming I have something wrong, what is it and how do I accomplish what I want? If you can't tell by looking at the statement right now just let me know..
Thanks in advance, here's my statement:
delete FROM Student S JOIN Purpose P on P.StudentID=S.StudentID join TestResults T on T.StudentID=S.StudentID WHERE S.ExamineeID=1062971
This will delete all records in Student with ExamineeID=1062971 but only if there are corresponding entries in Purpose and TestResults. If you want to delete the entries from all tables then define a cascade delete or use a transaction around the individual deletes.
begin tran delete Purpose from Purpose P, Student S where P.StudentID=S.StudentID and S.ExamineeID=1062971 delete TestResults from TestResults T, Student S where T.StudentID=S.StudentID and S.ExamineeID=1062971 delete Student where ExamineeID=1062971 commit tran
add your own error processing to this.
========================================== Cursors are useful if you don't know sql. DTS can be used in a similar way. Beer is not cold and it isn't fizzy.
This example shows the Transact-SQL extension used to delete records from a base table that is based on a join or correlated subquery. The first DELETE shows the SQL-92-compatible subquery solution, and the second DELETE shows the Transact-SQL extension. Both queries remove rows from the titleauthors table based on the titles stored in the titles table. /* SQL-92-Standard subquery */ USE pubs DELETE FROM titleauthor WHERE title_id IN (SELECT title_id FROM titles WHERE title LIKE '%computers%')
/* Transact-SQL extension */ USE pubs DELETE titleauthor FROM titleauthor INNER JOIN titles ON titleauthor.title_id = titles.title_id WHERE titles.title LIKE '%computers%
delete statement
Posted - 12/04/2002 : 12:18:12
