Geek asks and answers

Use query result as table name

I want to delete records in the parent table as well as in the child table using only one query. I m has the name of the child table CHILD1_TABLEin the parent table PARENT_TABLEin the name field TABLENAME. The parent table contains more child tables. I want to delete only one record from one of the child table, as well as the parent table. Common field IDin child and parent tables. I write my request as follows:

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 that does not work. Can anybody help me?

+3
source share
3 answers

How 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'
+2
source

Why not use ON DELETE CASCADE?

0
source 
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'

, .

0
source

More articles:


All Articles