Use query result as table name

Question

I want to delete records in parent table as well as child table using only one query. I m having the child table name CHILD1_TABLE in the Parent table PARENT_TABLE in the field name of TABLENAME. The parent table contains more child tables.I want to delete only one record from one of the child table as well parent table. The common field is ID in both child and parent tables. I write my query like this,

DELETE PARENT.*,CHILD.* 
FROM PARENT_TABLE PARENT 
INNER JOIN (SELECT TABLENAME FROM PARENT_TABLE WHERE ID='CHILD1-001') CHILD 
ON PARENT.ID=CHILD.ID 
WHERE PARENT.ID='CHILD1-001'

But it not works. Can anybody help me?

Maybe some of the ideas here help - [How to have Dynamic SQL in mysql stored procedure](http://stackoverflow.com/questions/190776/how-to-have-dynamic-sql-in-mysql-stored-procedure) — Randall, Nov 29 '16 at 22:29

score 2 · Answer 1 · answered Apr 17 '12 at 06:59

2

What about this:

DELETE PARENT.*,CHILD.* 
FROM PARENT_TABLE PARENT 
INNER JOIN (SELECT * FROM (SELECT TABLENAME FROM PARENT_TABLE WHERE ID='CHILD1-001') AS CHILD)  
ON PARENT.ID=CHILD.ID 
WHERE PARENT.ID='CHILD1-001'

answered Apr 17 '12 at 06:59

Code Prank

4,209
5
31
47

Change this to: (SELECT * FROM (SELECT TABLENAME FROM PARENT_TABLE WHERE ID='CHILD1-001') AS CHILD2) AS CHILD – Code Prank Apr 17 '12 at 07:20
i have tried it on my pc and it works. Did you notice the difference i have changed the table aliases – Code Prank Apr 17 '12 at 07:32
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/10157/discussion-between-sangeetha-krishnan-and-shayan-husaini) – Sangeetha Krishnan Apr 17 '12 at 07:37

score 0 · Answer 2 · answered Apr 17 '12 at 06:50

0

Why not use ON DELETE CASCADE?

answered Apr 17 '12 at 06:50

Euclides Mulémbwè

1,677
11
18

Thank you Euclides Mulémbwè.I am using MyISAM storage engine. – Sangeetha Krishnan Apr 17 '12 at 07:11

score 0 · Answer 3 · edited Feb 11 '13 at 21:47

0

DELETE PARENT.*,CHILD.* 
FROM PARENT_TABLE PARENT 
INNER JOIN (SELECT * FROM (SELECT TABLENAME FROM PARENT_TABLE WHERE ID='CHILD1-001') AS a)  as child
ON PARENT.ID=CHILD.ID 
WHERE PARENT.ID='CHILD1-001'

I am not sure for this query but give it a try.

edited Feb 11 '13 at 21:47

marc_s

732,580
175
1,330
1,459

answered Apr 17 '12 at 07:11

Ankit Sharma

3,923
2
29
49

Thank you for your reply Jason_vorhees. It gives the error Unknown table 'CHILD' in MULTI DELETE. – Sangeetha Krishnan Apr 17 '12 at 07:16

Use query result as table name

3 Answers3