如何在一个删除查询中使用多个表名 - 并非所有表都有数据

Question

What I need

I have a database called dbtuts and it has total 7 tables.

Table names are tbl_uploads, variationtable1, variationtable2, variationtable3, variationtable4, variationtable5, variationtable6.

All these tables have a column called prjId.

I want to delete a row from tbl_uploads table and from variationtable1 to variationtable6 whereby prjId is lets say xxxx .

But the problem is variationtable1 to variationtable6 may or may not have this xxxx data for prjId column.

Is there any way whereby I can execute a single query that will delete xxxx if there is such a data.

My Codes

I tried to do the following way, but it is not working.

$deleteQuery = "
DELETE 
  FROM dbtuts.tbl_uploads
     , dbtuts.variationtable1
     , dbtuts.variationtable2
     , dbtuts.variationtable3
     , dbtuts.variationtable4
     , dbtuts.variationtable5
     , dbtuts.variationtable6 
 WHERE prjId = 'xxxx'
 ";

$resultDeleteQuery = mysqli_query($conn,$deleteQuery);

Does anyone have some idea?

Answer 1

You can do it with a single query using LEFT JOIN:

DELETE t, v1, v2, v3, v4, v5, v6
  FROM dbtuts.tbl_uploads t
  LEFT JOIN dbtuts.variationtable1 v1 ON t.prjId = v1.prjId
  LEFT JOIN dbtuts.variationtable2 v2 ON t.prjId = v2.prjId
  LEFT JOIN dbtuts.variationtable3 v3 ON t.prjId = v3.prjId
  LEFT JOIN dbtuts.variationtable4 v4 ON t.prjId = v4.prjId
  LEFT JOIN dbtuts.variationtable5 v5 ON t.prjId = v5.prjId
  LEFT JOIN dbtuts.variationtable6 v6 ON t.prjId = v6.prjId
WHERE t.prjId = 'xxxx'

Demo here

Note: This kind of query wouldn't be necessary if your database schema was properly designed. The correct way to implement this kind of funcionality is to create foreign keys in each of the dbtuts.variationtablex tables with ON CASCADE DELETE.