3

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
Sam
  • 7,252
  • 16
  • 46
  • 65
Bryan
  • 17,201
  • 24
  • 97
  • 123

5 Answers5

10

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

SQLMenace
  • 132,095
  • 25
  • 206
  • 225
  • 3
    Like Tom warns below, use the cascade with caution as you'll suddenly find data "missing" – Nick DeVore Jul 07 '09 at 20:05
  • if you have a PK and you delete it which also deletes the FK data how is that missing if the PK data is not there anymore..where does the FK point to? – SQLMenace Jul 07 '09 at 20:07
  • 1
    SQLMenace - Point taken. What can I say - cascading deletes have just always made me feel out of control. Perhaps an area of growth :) – Nick DeVore Jul 07 '09 at 20:15
  • Why do you use JOIN and ON instead ',' and WHERE? – Bryan Jul 07 '09 at 20:26
  • 1
    because that is old style ANSI syntax and especially for LEFT JOINs that syntax is not allowed anymore on 2008 *= and =* won't parse – SQLMenace Jul 07 '09 at 20:29
  • And you say, that 68.3k rep and 11 goldies allows you to write garbage language, with no capital letters, periods and commas? Interesting... – trejder Jul 01 '14 at 08:49
3

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.

eKek0
  • 23,005
  • 25
  • 91
  • 119
1

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.

Tom Hubbard
  • 15,820
  • 14
  • 59
  • 86
0

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.

John Wagenleitner
  • 10,967
  • 1
  • 40
  • 39
0

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

u07ch
  • 13,324
  • 5
  • 42
  • 48