0

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?

Anu
  • 1,123
  • 2
  • 13
  • 42
  • Do all those tables mentioned in the delete statement have this column `prjId`? – cdaiga Jun 01 '18 at 08:43
  • Why not have one variationtable instead of 6? – Strawberry Jun 01 '18 at 08:43
  • @cdaiga Yes. All tables mentioned in the delete statement have this column prjId – Anu Jun 01 '18 at 08:45
  • @Strawberry All these tables have many data. That is why i separate it into 6 thinking that will be good. isn't it? – Anu Jun 01 '18 at 08:46
  • You can use foreign keys and `on cascade delete`, also you can use `DELETE ... JOIN` – NobbyNobbs Jun 01 '18 at 08:46
  • [How to use multiple table names in one delete query](https://stackoverflow.com/questions/3331992/how-to-delete-from-multiple-tables-in-mysql) – cdaiga Jun 01 '18 at 08:47
  • Possible duplicate of [How to delete from multiple tables in MySQL?](https://stackoverflow.com/questions/3331992/how-to-delete-from-multiple-tables-in-mysql) – Nigel Ren Jun 01 '18 at 08:48
  • @Anu No. If tables become large - something over a million rows, say, then look at partitioning the tables. Giorgos's answer is the best of the answers posted below, but possibly a poor choice when compared with proper partitioning. – Strawberry Jun 01 '18 at 08:51
  • https://stackoverflow.com/questions/1233451/delete-from-two-tables-in-one-query?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa – Salim Ibrohimi Jun 01 '18 at 08:55
  • Possible duplicate of [delete from two tables in one query](https://stackoverflow.com/questions/1233451/delete-from-two-tables-in-one-query) – Salim Ibrohimi Jun 01 '18 at 08:55

2 Answers2

2

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.

Giorgos Betsos
  • 71,379
  • 9
  • 63
  • 98
  • This perfectly works for me. I am new to mysql. Thus my data structuring may not be good. I didn't quite understand your codes though. I will try to get more grip about JOIN method in MySQL. Thank you – Anu Jun 01 '18 at 09:05
1

This would be the most easy way to do it and in 1 query

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

Please remember to use prepared statements when you execute this query so you prevent your project against sql injection - Thanks to Strawberry for mentioning You could do it like this

$stmt = $mysqli->prepare("DELETE FROM dbtuts.tbl_uploads WHERE prjId = 'xxxx';
                          DELETE FROM dbtuts.variationtable1 WHERE prjId = 'xxxx';
                          DELETE FROM dbtuts.variationtable2 WHERE prjId = 'xxxx';
                          DELETE FROM dbtuts.variationtable3 WHERE prjId = 'xxxx';
                          DELETE FROM dbtuts.variationtable4 WHERE prjId = 'xxxx';
                          DELETE FROM dbtuts.variationtable5 WHERE prjId = 'xxxx';
                          DELETE FROM dbtuts.variationtable6 WHERE prjId = 'xxxx'";);
$stmt->execute(); 
$stmt->close();
Ende
  • 303
  • 2
  • 4
  • 24