subject:"RE\: very tough query"

Re: very tough query

2002-03-05 Thread Nathan


Well after reviewing the DELETE section of the manual, I see nothing to suggest that 
it supports
multiple-table deletes...

# Nathan

- Original Message -
From: "Daren Cotter" <[EMAIL PROTECTED]>
To: "Nathan" <[EMAIL PROTECTED]>; "Daren Cotter" <[EMAIL PROTECTED]>
Sent: Tuesday, March 05, 2002 12:37 PM
Subject: RE: very tough query


Here's what happens:

ERROR 1064: You have an error in your SQL syntax near ' poll_answers,
poll_votes where (poll_questions.poll_id = poll_answers.poll_id) ' at line 1



-Original Message-
From: Nathan [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, March 05, 2002 9:28 AM
To: Daren Cotter
Subject: Re: very tough query


I haven't read this thread but I thought you couldn't have anything besides
a priority between
DELETE and FROM...

What happens with:

mysql> DELETE FROM
->   poll_questions, poll_answers, poll_votes
-> WHERE
->   (poll_questions.poll_id = poll_answers.poll_id)
->   AND (poll_answers.answer_id = poll_votes.answer_id)
->   AND (poll_questions.poll_id = 1);


# Nathan


- Original Message -
From: "Daren Cotter" <[EMAIL PROTECTED]>
To: "Batara Kesuma" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Cc: "Daren Cotter" <[EMAIL PROTECTED]>
Sent: Tuesday, March 05, 2002 11:42 AM
Subject: RE: very tough query


Thanks much for the try, but I get an error:

mysql> DELETE
->   poll_questions, poll_answers, poll_votes
-> FROM
->   poll_questions, poll_answers, poll_votes
-> WHERE
->   (poll_questions.poll_id = poll_answers.poll_id)
->   AND (poll_answers.answer_id = poll_votes.answer_id)
->   AND (poll_questions.poll_id = 1);
ERROR 1064: You have an error in your SQL syntax near 'poll_questions,
poll_answers, poll_votes
FROM
  poll_questions, poll_answers, ' at line 2



-Original Message-
From: Batara Kesuma [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, March 05, 2002 8:27 AM
To: [EMAIL PROTECTED]
Cc: Daren Cotter
Subject: Re: very tough query


Hi Darren,

On Tue, 5 Mar 2002 09:42:50 -0800
"Daren Cotter" <[EMAIL PROTECTED]> wrote:

> mysql> desc poll_questions;
> ++-+--+-++-
> ---+
> | Field  | Type| Null | Key | Default| Extra
> |
> ++-+--+-++-
> ---+
> | poll_id| int(9) unsigned |  | PRI | NULL   |
> auto_increment |
> | aim_screenname | varchar(16) |  | MUL ||
> |
> | question   | varchar(255)|  | ||
> |
> | date_created   | date|  | | -00-00 |
> |
> ++-+--+-++-
> ---+
>
> mysql> desc poll_answers;
> +---+--+--+-+-++
> | Field | Type | Null | Key | Default | Extra  |
> +---+--+--+-+-++
> | answer_id | int(12) unsigned |  | PRI | NULL| auto_increment |
> | poll_id   | int(9) unsigned  |  | | 0   ||
> | answer| varchar(255) |  | | ||
> +---+--+--+-+-++
>
> mysql> desc poll_votes;
> ++--+--+-+-+---+
> | Field  | Type | Null | Key | Default | Extra |
> ++--+--+-+-+---+
> | aim_screenname | varchar(16)  |  | PRI | |   |
> | answer_id  | int(12) unsigned |  | PRI | 0   |   |
> | time_voted | timestamp(12)| YES  | | NULL|   |
> | voter_ip   | varchar(15)  |  | | |   |
> ++--+--+-+-+---+
>
> DELETE FROM poll_votes AS v, poll_answers AS a, poll_questions AS q
> WHERE (q.poll_id = 1) AND (v.answer_id = a.answer_id) AND
> (a.poll_id = q.poll_id);

I haven't tried it yet, but maybe you can try:

DELETE
  poll_questions, poll_answers, poll_votes
FROM
  poll_questions, poll_answers, poll_votes
WHERE
  (poll_questions.poll_id = poll_answers.poll_id)
  AND (poll_answers.answer_id = poll_votes.answer_id)
  AND (poll_questions.poll_id = 1);


--bk


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
<[EMAIL PROTECTED]>
Trouble unsubscr

Re: very tough query

2002-03-05 Thread Rodney Broom


From: Daren Cotter <[EMAIL PROTECTED]>

> ...what happens when I want to delete a poll?

Do it machanically. That is, do a query in advance to find the data that needs to be 
deleted, then go through and delete it. The logic for joining a delete can be written, 
but MySQL doesn't support it. (Hey folks, does anything else support joined deletes?)


By the way, since poll_votes has a PK of aim_screenname/answer_id, folks can give each 
answer for a given poll once. As apposed to just one answer per poll.



---
Rodney Broom
Programmer: Desert.Net




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: very tough query

2002-03-05 Thread Daren Cotter


Thanks much for the try, but I get an error:

mysql> DELETE
->   poll_questions, poll_answers, poll_votes
-> FROM
->   poll_questions, poll_answers, poll_votes
-> WHERE
->   (poll_questions.poll_id = poll_answers.poll_id)
->   AND (poll_answers.answer_id = poll_votes.answer_id)
->   AND (poll_questions.poll_id = 1);
ERROR 1064: You have an error in your SQL syntax near 'poll_questions,
poll_answers, poll_votes
FROM
  poll_questions, poll_answers, ' at line 2



-Original Message-
From: Batara Kesuma [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, March 05, 2002 8:27 AM
To: [EMAIL PROTECTED]
Cc: Daren Cotter
Subject: Re: very tough query


Hi Darren,

On Tue, 5 Mar 2002 09:42:50 -0800
"Daren Cotter" <[EMAIL PROTECTED]> wrote:

> mysql> desc poll_questions;
> ++-+--+-++-
> ---+
> | Field  | Type| Null | Key | Default| Extra
> |
> ++-+--+-++-
> ---+
> | poll_id| int(9) unsigned |  | PRI | NULL   |
> auto_increment |
> | aim_screenname | varchar(16) |  | MUL ||
> |
> | question   | varchar(255)|  | ||
> |
> | date_created   | date|  | | -00-00 |
> |
> ++-+--+-++-
> ---+
>
> mysql> desc poll_answers;
> +---+--+--+-+-++
> | Field | Type | Null | Key | Default | Extra  |
> +---+--+--+-+-++
> | answer_id | int(12) unsigned |  | PRI | NULL| auto_increment |
> | poll_id   | int(9) unsigned  |  | | 0   ||
> | answer| varchar(255) |  | | ||
> +---+--+--+-+-++
>
> mysql> desc poll_votes;
> ++--+--+-+-+---+
> | Field  | Type | Null | Key | Default | Extra |
> ++--+--+-+-+---+
> | aim_screenname | varchar(16)  |  | PRI | |   |
> | answer_id  | int(12) unsigned |  | PRI | 0   |   |
> | time_voted | timestamp(12)| YES  | | NULL|   |
> | voter_ip   | varchar(15)  |  | | |   |
> ++--+--+-+-+---+
>
> DELETE FROM poll_votes AS v, poll_answers AS a, poll_questions AS q
>   WHERE (q.poll_id = 1) AND (v.answer_id = a.answer_id) AND
>   (a.poll_id = q.poll_id);

I haven't tried it yet, but maybe you can try:

DELETE
  poll_questions, poll_answers, poll_votes
FROM
  poll_questions, poll_answers, poll_votes
WHERE
  (poll_questions.poll_id = poll_answers.poll_id)
  AND (poll_answers.answer_id = poll_votes.answer_id)
  AND (poll_questions.poll_id = 1);


--bk


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: very tough query

2002-03-05 Thread Batara Kesuma


Hi Darren,

On Tue, 5 Mar 2002 09:42:50 -0800
"Daren Cotter" <[EMAIL PROTECTED]> wrote:

> mysql> desc poll_questions;
> ++-+--+-++-
> ---+
> | Field  | Type| Null | Key | Default| Extra
> |
> ++-+--+-++-
> ---+
> | poll_id| int(9) unsigned |  | PRI | NULL   |
> auto_increment |
> | aim_screenname | varchar(16) |  | MUL ||
> |
> | question   | varchar(255)|  | ||
> |
> | date_created   | date|  | | -00-00 |
> |
> ++-+--+-++-
> ---+
> 
> mysql> desc poll_answers;
> +---+--+--+-+-++
> | Field | Type | Null | Key | Default | Extra  |
> +---+--+--+-+-++
> | answer_id | int(12) unsigned |  | PRI | NULL| auto_increment |
> | poll_id   | int(9) unsigned  |  | | 0   ||
> | answer| varchar(255) |  | | ||
> +---+--+--+-+-++
> 
> mysql> desc poll_votes;
> ++--+--+-+-+---+
> | Field  | Type | Null | Key | Default | Extra |
> ++--+--+-+-+---+
> | aim_screenname | varchar(16)  |  | PRI | |   |
> | answer_id  | int(12) unsigned |  | PRI | 0   |   |
> | time_voted | timestamp(12)| YES  | | NULL|   |
> | voter_ip   | varchar(15)  |  | | |   |
> ++--+--+-+-+---+
> 
> DELETE FROM poll_votes AS v, poll_answers AS a, poll_questions AS q
>   WHERE (q.poll_id = 1) AND (v.answer_id = a.answer_id) AND
>   (a.poll_id = q.poll_id);

I haven't tried it yet, but maybe you can try:

DELETE 
  poll_questions, poll_answers, poll_votes 
FROM 
  poll_questions, poll_answers, poll_votes 
WHERE
  (poll_questions.poll_id = poll_answers.poll_id) 
  AND (poll_answers.answer_id = poll_votes.answer_id)
  AND (poll_questions.poll_id = 1);


--bk

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: very tough query

Re: very tough query

RE: very tough query

Re: very tough query

4 matches

Site Navigation

Mail list logo

Footer information