MySQL - Delete across two tables. OT

[Chris]

Hi, sorry to post OT but i cant find the MySQL newsgroup, however i am
hoping to pick up on some expert advice from php/mysql gurus here. I'm
having some trouble performing a delete across two tables.

The tables i have are:

questionnaires (id, name);

questionnaire_q uestions (questionnaires _id, id, name, qf_type)

The questionnaire_q uestions table contains a list of questions for a
specific questionnaire (indicated by the questonnaires_i d). I would
like to remove a questionnaire, and when doing so all corresponding
questionnaire_q uestions who have matching id's (questionnaires .id =
questionnaire_q uestions.questi onnaires_id).

I have this statement, however it only works if a questionnaire has
questions... however i would like it to delete even if no questions
exist for that specific questionnaire. The SQL i am using is:

delete questionnaires, questionnaire_q uestions FROM questionnaires,
questionnaire_q uestions WHERE questionnaires. id =
questionnaire_q uestions.questi onnaires_id AND questionnaires. id =
THE_QUESTIONAIR E_TO_DELETE_ID

Thanks in advance, any help much appreciated.

Chris

[Jerry Gitomer]

Re: MySQL - Delete across two tables. OT

Chris wrote:
[color=blue]
> Hi, sorry to post OT but i cant find the MySQL newsgroup,
> however i am hoping to pick up on some expert advice from
> php/mysql gurus here. I'm having some trouble performing a
> delete across two tables.
>
> The tables i have are:
>
> questionnaires (id, name);
>
> questionnaire_q uestions (questionnaires _id, id, name, qf_type)
>
> The questionnaire_q uestions table contains a list of questions
> for a specific questionnaire (indicated by the
> questonnaires_i d). I would like to remove a questionnaire, and
> when doing so all corresponding questionnaire_q uestions who
> have matching id's (questionnaires .id =
> questionnaire_q uestions.questi onnaires_id).
>
> I have this statement, however it only works if a
> questionnaire has questions... however i would like it to
> delete even if no questions exist for that specific
> questionnaire. The SQL i am using is:
>
> delete questionnaires, questionnaire_q uestions FROM
> questionnaires, questionnaire_q uestions WHERE
> questionnaires. id = questionnaire_q uestions.questi onnaires_id
> AND questionnaires. id = THE_QUESTIONAIR E_TO_DELETE_ID
>
> Thanks in advance, any help much appreciated.
>
> Chris[/color]

It is safer to do this in two steps: First delete the questions
and then delete the questionaire. The reason I say this is
that the optimizer (either now or in the future) may decide to
delete the questionaire first and then delete the questions --
which is fine as long as the transaction always runs to
completion. The problem is that if the transaction doesn't run
to completion you can wind up with orphaned questions.
--
Jerry Gitomer

[Steve]

Re: MySQL - Delete across two tables. OT

If you have the questionaire ID, then just use 2 queries.

DELETE FROM questionaire_qu estions
WHERE f_questionaireI D = QUESTIONAIREID

DELETE FROM questionaires
WHERE questionaireID = QUESTIONAIREID

Unless of course you are not working in a semi-persistent environment
(like PHP)...if not, you may have to create variables in the mysql
itself (go to mysql.com for this, because I havent done it in years)



Chris wrote:[color=blue]
> Hi, sorry to post OT but i cant find the MySQL newsgroup, however i[/color]
am[color=blue]
> hoping to pick up on some expert advice from php/mysql gurus here.[/color]
I'm[color=blue]
> having some trouble performing a delete across two tables.
>
> The tables i have are:
>
> questionnaires (id, name);
>
> questionnaire_q uestions (questionnaires _id, id, name, qf_type)
>
> The questionnaire_q uestions table contains a list of questions for a
> specific questionnaire (indicated by the questonnaires_i d). I would
> like to remove a questionnaire, and when doing so all corresponding
> questionnaire_q uestions who have matching id's (questionnaires .id =
> questionnaire_q uestions.questi onnaires_id).
>
> I have this statement, however it only works if a questionnaire has
> questions... however i would like it to delete even if no questions
> exist for that specific questionnaire. The SQL i am using is:
>
> delete questionnaires, questionnaire_q uestions FROM questionnaires,
> questionnaire_q uestions WHERE questionnaires. id =
> questionnaire_q uestions.questi onnaires_id AND questionnaires. id =
> THE_QUESTIONAIR E_TO_DELETE_ID
>
> Thanks in advance, any help much appreciated.
>
> Chris[/color]

[Andy Hassall]

Re: MySQL - Delete across two tables. OT

On 6 Dec 2004 07:19:30 -0800, g18c@hotmail.co m (Chris) wrote:
[color=blue]
>Hi, sorry to post OT but i cant find the MySQL newsgroup, however i am
>hoping to pick up on some expert advice from php/mysql gurus here. I'm
>having some trouble performing a delete across two tables.
>
>The tables i have are:
>
>questionnair es (id, name);
>
>questionnaire_ questions (questionnaires _id, id, name, qf_type)
>
>The questionnaire_q uestions table contains a list of questions for a
>specific questionnaire (indicated by the questonnaires_i d). I would
>like to remove a questionnaire, and when doing so all corresponding
>questionnaire_ questions who have matching id's (questionnaires .id =
>questionnaire_ questions.quest ionnaires_id).
>
>I have this statement, however it only works if a questionnaire has
>questions... however i would like it to delete even if no questions
>exist for that specific questionnaire. The SQL i am using is:
>
>delete questionnaires, questionnaire_q uestions FROM questionnaires,
>questionnaire_ questions WHERE questionnaires. id =
>questionnaire_ questions.quest ionnaires_id AND questionnaires. id =
>THE_QUESTIONAI RE_TO_DELETE_ID[/color]

Add a foreign key constraint from questionnaire_q uestions to questionnaires
(you should have this anyway) and declare it as 'on delete cascade'.

http://dev.mysql.com/doc/mysql/en/InnoDB_foreign_key_constraints.html

mysql> create table questionnaires (
-> id int not null auto_increment primary key,
-> name varchar(16) not null
-> )
-> TYPE=INNODB;
Query OK, 0 rows affected (0.35 sec)

mysql> create table questionnaire_q uestions (
-> questionnaires_ id int not null,
-> id int not null auto_increment primary key,
-> name varchar(16) not null,
-> index (questionnaires _id),
-> foreign key (questionnaires _id)
-> references questionnaires (id)
-> on delete cascade
-> )
-> TYPE=INNODB;
Query OK, 0 rows affected (0.03 sec)

mysql> insert into questionnaires values (null, 'Quest.1');
Query OK, 1 row affected (0.05 sec)

mysql> insert into questionnaires values (null, 'Quest.2');
Query OK, 1 row affected (0.00 sec)

mysql> insert into questionnaire_q uestions values (1, null, 'Q1');
Query OK, 1 row affected (0.02 sec)

mysql> select * from questionnaires;
+----+---------+
| id | name |
+----+---------+
| 1 | Quest.1 |
| 2 | Quest.2 |
+----+---------+
2 rows in set (0.03 sec)

mysql> select * from questionnaire_q uestions;
+-------------------+----+------+
| questionnaires_ id | id | name |
+-------------------+----+------+
| 1 | 1 | Q1 |
+-------------------+----+------+
1 row in set (0.01 sec)

mysql> delete from questionnaires;
Query OK, 2 rows affected (0.08 sec)

mysql> select * from questionnaire_q uestions;
Empty set (0.00 sec)

--
Andy Hassall / <andy@andyh.co. uk> / <http://www.andyh.co.uk >
<http://www.andyhsoftwa re.co.uk/space> Space: disk usage analysis tool

[g18c@hotmail.com]

Re: MySQL - Delete across two tables. OT

Many thanks for spending the time and replying chaps, you have all been
a great help. Whilst waiting for replies i did indeed implement a two
stage 'delete'... however the cascade approach is interesting.
Thanks again :)

Chris