CODEWITHSUNDEEP
mysqlsql
rocking
rocking

Reputation: 4899

How to delete from a table interlinking 4 tables

Hi following are my 4 tables.

client_parent_question :-

+----+------------+------------+---------+-----+------+------+
| id | is_deleted | sort_order | version | cid | pid  | qid  |
+----+------------+------------+---------+-----+------+------+
|  1 |            |          1 |       0 |   1 |    1 |    1 |
|  2 |            |          2 |       0 |   1 |    1 |    2 |
|  3 |            |          3 |       0 |   1 |    1 |    3 |
|  4 |            |          4 |       0 |   1 |    1 |    4 |
|  5 |            |          1 |       0 |   1 |    2 |    7 |
+----+------------+------------+---------+-----+------+------+

mysql> select * from client_parent;

+----+------------+------------+---------+-----+------+
| id | is_deleted | sort_order | version | cid | pid  |
+----+------------+------------+---------+-----+------+
|  1 |            |          1 |       0 |   1 |    1 |
|  2 |            |          2 |       0 |   1 |    2 |
+----+------------+------------+---------+-----+------+
2 rows in set (0.00 sec)

mysql> select * from client_question;

+----+------------+---------+-----+------+------+
| id | is_deleted | version | cid | pqid | qtid |
+----+------------+---------+-----+------+------+
|  1 |            |       0 |   1 |    1 |    1 |
|  2 |            |       0 |   1 |    2 |    4 |
|  3 |            |       0 |   1 |    2 |    4 |
|  4 |            |       0 |   1 |    1 |    1 |
|  5 |            |       0 |   1 |    2 |    4 |
|  6 |            |       0 |   1 |    3 |    4 |
|  7 |            |       0 |   1 |    3 |    4 |
|  8 |            |       0 |   1 |    1 |    1 |
|  9 |            |       0 |   1 |    2 |    4 |
| 10 |            |       0 |   1 |    3 |    4 |
| 11 |            |       0 |   1 |    4 |    4 |
| 12 |            |       0 |   1 |    4 |    4 |
+----+------------+---------+-----+------+------+

mysql> select * from client_question_option;

+----+------------+---------+------+------+
| id | is_deleted | version | cqid | oid  |
+----+------------+---------+------+------+
|  1 |            |       0 |    2 |    1 |
|  2 |            |       0 |    3 |    4 |
|  3 |            |       0 |    6 |    2 |
|  4 |            |       0 |    7 |    3 |
|  5 |            |       0 |   11 |    1 |
|  6 |            |       0 |   12 |    4 |
|  7 |            |       0 |   14 |    1 |
|  8 |            |       0 |   15 |    4 |
+----+------------+---------+------+------+

I know only cid and pid of client_parent table

My aim is to delete all from client_question,client_parent_question and client_question_option

In the client_question_option cqid id is the id of client_question table

Following is the sqlfiddle

I did 

DELETE FROM cqo,qo,cpq client_question_option cqo ,client_question cq,client_parent_question ,client_parent cp
WHERE cqo.cqid=cq.id AND cq.pqid=pq.id AND cqo.oid=qo.id AND cq.cid=1  AND cp.pid=1

But this did not work.

Upvotes: 2

Views: 67

Answers (4)

NaveenThally
NaveenThally

Reputation: 1014

You have an option in MYSQL Called as ONDELETE CASCADE where in when you set this on a key

example : foreign key(cid) references client_parent(cid) ON DELETE CASCADE when you delete the key based on your condition all referencing ids will be deleted , so one simple query to delete parent row is enough for it to delete all child rows present in other table data

Advantages of using ON DELETE CASCADE

  1. you are not require to right innerjoins and delete data manually
  2. you dont get parent key errors
  3. you can save up your time

Thats it NJOY!!!

Upvotes: 0

Fabricator
Fabricator

Reputation: 12772

You just need to move the FROM keyword after the table aliases. See multiple-table syntax

DELETE cqo, qo, cpq 
FROM client_question_option cqo,
     client_question cq,
     client_parent_question,
     client_parent cp
WHERE cqo.cqid=cq.id AND cq.pqid=pq.id AND cqo.oid=qo.id 
      AND cq.cid=1 AND cp.pid=1;

Upvotes: 2

Gabber
Gabber

Reputation: 7249

using inner join

DELETE  cqo,cq,cp,pq 
FROM client_question_option cqo 
INNER JOIN client_question cq 
INNER JOIN client_parent_question as pq 
INNER JOIN client_parent cp
WHERE cqo.cqid=cq.id AND cq.pqid=pq.id AND cqo.oid=cqo.id AND cq.cid=1  AND cp.pid=1

Upvotes: 1

Ankit Bajpai
Ankit Bajpai

Reputation: 13509

Your DELETE query's syntax is wrong, which must be somthing like this:-

DELETE cqo,cp,pq 
FROM client_question_option cqo, client_question cq, client_parent_question pq, client_parent cp
WHERE cqo.cqid=cq.id AND cq.pqid=pq.id AND cqo.oid=cp.id AND cq.cid=1  AND cp.pid=1;

Hope this will help you.

Upvotes: 1

Related Questions