Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mikejohnson
Posting Yak Master
153 Posts |
 Posted - 2002-12-04 : 12:18:12
|
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
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-12-04 : 12:32:23
|
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. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2002-12-04 : 15:41:42
|
Shouldn't the statement be:
delete Student
FROM Student S
JOIN Purpose P on P.StudentID=S.StudentID
join TestResults T on T.StudentID=S.StudentID
WHERE S.ExamineeID=1062971
I believe that you need to list the table twice when doing a delete.
|
|
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
Posted - 2002-12-04 : 16:03:17
|
FROM BOL:
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%
Edited by - ValterBorges on 12/04/2002 16:04:17 |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2002-12-04 : 16:26:55
|
I should have said in my last post:
I believe that you need to list the table twice when doing a delete with a join.
In your example that would be the t-sql extension.
|
|
|
|
|
|
|
delete statement