SQL delete from related tables

Question

I am trying to delete all records that match a quiz id from the question table and the relationship table. The query works for a select statement, but won't allow the same statement to delete. @quizId is a quiz Id value I pass into my stored procedure. Does anyone know how to delete from both tables using one statement? Is it possible?

DELETE tGeoQuestions as a, tGeoQuizToQuestion as b WHERE b.quizId = @quizId AND a.id = b.questionid

Thanks

Answer 1

You need to enable cascade delete then it would happen automagically, all you need to do is delete from the table with the PK and all the fk tables will be deleted automatically

otherwise it is a 2 step operation

something like this, put this in a tran

DELETE a 
FROM tGeoQuestions as a
JOIN tGeoQuizToQuestion as b 
ON a.id = b.questionid
AND b.quizId = @quizId


DELETE tGeoQuizToQuestion  WHERE quizId = @quizId

your 3rd option is a trigger on the PK table that deletes everything from the FK table if it gets deleted in the PK table...I would't recommend the trigger

Answer 2

Far as I know this isn't possible in a single SQL statement. If you have the proper relationship setup the delete would cascade automatically. Else you'll have to issue 2 delete statements.

Answer 3

You can do this in mysql delete the manual shows the method as

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
tbl_name[.*] [, tbl_name[.*] ...]
FROM table_references
[WHERE where_definition]

In SQL server i think you either need to use foreign keys or delete from questions where quiz = ... and then from the quiz table

Answer 4

I'm not sure it is possible to delete from two tables in the same statement in the same way you can select from two. It is at least not possible in Oracle.

As SQLMenace mentioned your best bet is to turn on cascade.

Be carefull with Cascade though, If you have it very ingrained in you structure it becomes very easy to wipe out a LOT of data.

Answer 5

Try this:

DELETE a
FROM tGeoQuestions as a
INNER JOIN tGeoQuizToQuestion as b ON a.id = b.questionid
WHERE b.quizId = @quizId

By the way, your select statement acctually works (and I don't know wich is your statement...).

You must replace only

SELECT ...

with

DELETE [table name or alias]

and leave everything else the same.