RE: [PHP-DB] delete statement question
Per my original email; you must be running MYSQL 4.0 or later. If you are runnig version 3.x, as it appears you are, then this suggestion will NOT work -Original Message- From: Andrés Felipe Hernández [mailto:[EMAIL PROTECTED]] Sent: Monday, March 25, 2002 5:59 PM To: [EMAIL PROTECTED] Subject: Re: [PHP-DB] delete statement question Rick, thanks for the idea but actually it wouldnt work :( take a look: mysql> delete answers -> from exam e, questions q, answers a -> where a.question_id=q.question_id and q.exam_id=e.exam_id; ERROR 1064: You have an error in your SQL syntax near 'answers from exam e, questions q, exam_an' at line 1 so i think the problem is that you cant do "DELETE something FROM..." i think the solution is more like the one Bill Morrow proposed, even though he is using a subselect and i am using my sql. Any ideas? thanks for the help so far, andres - Original Message - From: "Rick Emery" <[EMAIL PROTECTED]> To: "Andr?s Felipe Hern?ndez" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Monday, March 25, 2002 12:03 PM Subject: RE: [PHP-DB] delete statement question > this solution will not work for mysql databases, as mysql does not support > sub-selects > > -Original Message- > From: Bill Morrow [mailto:[EMAIL PROTECTED]] > Sent: Monday, March 25, 2002 2:02 PM > To: Andr?s Felipe Hern?ndez > Cc: [EMAIL PROTECTED] > Subject: Re: [PHP-DB] delete statement question > > > On Mon, Mar 25, 2002 at 02:42:08PM -0800, Andr?s Felipe Hern?ndez wrote: > > Hi, I hope you can help me with this: > > > > I have these 3 tables. > > > > exam ( > > exam_id > > ) > > > > questions ( > > question_id > > exam_id > > ) > > > > answers ( > > answer_id > > question_id > > ) > > > > I am wondering if i can delete all the rows for answers linked to a given > > exam using only one delete statement. > > > > Thanks in advance, > > > > andres > > > > delete answers > where question_id in (select question_id from questions where exam_id=X) > > I assume there isn't a one-to-one relationship between questions and > answers? If there is, your database is overnormalized. > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] delete statement question
Rick, thanks for the idea but actually it wouldnt work :( take a look: mysql> delete answers -> from exam e, questions q, answers a -> where a.question_id=q.question_id and q.exam_id=e.exam_id; ERROR 1064: You have an error in your SQL syntax near 'answers from exam e, questions q, exam_an' at line 1 so i think the problem is that you cant do "DELETE something FROM..." i think the solution is more like the one Bill Morrow proposed, even though he is using a subselect and i am using my sql. Any ideas? thanks for the help so far, andres - Original Message - From: "Rick Emery" <[EMAIL PROTECTED]> To: "Andr?s Felipe Hern?ndez" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Monday, March 25, 2002 12:03 PM Subject: RE: [PHP-DB] delete statement question > this solution will not work for mysql databases, as mysql does not support > sub-selects > > -Original Message- > From: Bill Morrow [mailto:[EMAIL PROTECTED]] > Sent: Monday, March 25, 2002 2:02 PM > To: Andr?s Felipe Hern?ndez > Cc: [EMAIL PROTECTED] > Subject: Re: [PHP-DB] delete statement question > > > On Mon, Mar 25, 2002 at 02:42:08PM -0800, Andr?s Felipe Hern?ndez wrote: > > Hi, I hope you can help me with this: > > > > I have these 3 tables. > > > > exam ( > > exam_id > > ) > > > > questions ( > > question_id > > exam_id > > ) > > > > answers ( > > answer_id > > question_id > > ) > > > > I am wondering if i can delete all the rows for answers linked to a given > > exam using only one delete statement. > > > > Thanks in advance, > > > > andres > > > > delete answers > where question_id in (select question_id from questions where exam_id=X) > > I assume there isn't a one-to-one relationship between questions and > answers? If there is, your database is overnormalized. > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] delete statement question
this solution will not work for mysql databases, as mysql does not support sub-selects -Original Message- From: Bill Morrow [mailto:[EMAIL PROTECTED]] Sent: Monday, March 25, 2002 2:02 PM To: Andr?s Felipe Hern?ndez Cc: [EMAIL PROTECTED] Subject: Re: [PHP-DB] delete statement question On Mon, Mar 25, 2002 at 02:42:08PM -0800, Andr?s Felipe Hern?ndez wrote: > Hi, I hope you can help me with this: > > I have these 3 tables. > > exam ( > exam_id > ) > > questions ( > question_id > exam_id > ) > > answers ( > answer_id > question_id > ) > > I am wondering if i can delete all the rows for answers linked to a given > exam using only one delete statement. > > Thanks in advance, > > andres > delete answers where question_id in (select question_id from questions where exam_id=X) I assume there isn't a one-to-one relationship between questions and answers? If there is, your database is overnormalized. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] delete statement question
if you are using mysql 4.x, you might try: DELETE answers FROM exam e, questions q, answers a WHERE a.question_id=q.question_id && q.exam_id=e.exam_id; I've not tested this, though. -Original Message- From: Andrés Felipe Hernández [mailto:[EMAIL PROTECTED]] Sent: Monday, March 25, 2002 4:42 PM To: [EMAIL PROTECTED] Subject: [PHP-DB] delete statement question Hi, I hope you can help me with this: I have these 3 tables. exam ( exam_id ) questions ( question_id exam_id ) answers ( answer_id question_id ) I am wondering if i can delete all the rows for answers linked to a given exam using only one delete statement. Thanks in advance, andres -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] delete statement question
On Mon, Mar 25, 2002 at 02:42:08PM -0800, Andr?s Felipe Hern?ndez wrote: > Hi, I hope you can help me with this: > > I have these 3 tables. > > exam ( > exam_id > ) > > questions ( > question_id > exam_id > ) > > answers ( > answer_id > question_id > ) > > I am wondering if i can delete all the rows for answers linked to a given > exam using only one delete statement. > > Thanks in advance, > > andres > delete answers where question_id in (select question_id from questions where exam_id=X) I assume there isn't a one-to-one relationship between questions and answers? If there is, your database is overnormalized. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
