CODEWITHSUNDEEP
mysql
Sangeetha Krishnan
Sangeetha Krishnan

Reputation: 1001

Use query result as table name

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?

Upvotes: 3

Views: 3306

Answers (3)

Ankit Sharma
Ankit Sharma

Reputation: 4071

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.

Upvotes: 0

Code Prank
Code Prank

Reputation: 4250

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'

Upvotes: 2

Euclides Mulémbwè
Euclides Mulémbwè

Reputation: 1687

Why not use ON DELETE CASCADE?

Upvotes: 0

Related Questions