Delete from multiple tables at once

[Adamhumbug]

Hi All,

I am adding a button that will delete many things in the system which all have the same $job_id

There is going to be 10 or so tables that need to delete *  where $job_id = ?

Is there a better way to do this rather than just iterating my code 10 times.

I was looking at just joining but isnt one benefit of prepared statements that they can be used again and again to increase speed.

[ginerjm]

IIRC one cannot use prepared statements using table names as an argument.  For what you describe I would write a function that has a query statement and query execution for each of your tables.  The function would use your 'job id' as the its lone parameter which you could use in a "parms array" that you reference in each of your execute calls (assuming that you are using PDO).

A pseudo-example:

Quote

function Delete_Jobs($pdo, $job_id)

{

$parms['job_id'] = $job_id;   // do this line once

//  Delete table 1

$q = 'delete from table_1 where job_id=:job_id';

$qst = $pdo->prepare($q);

$qst->execute($parms);

// repeat for tables 2-n

}

 

Edited December 16, 2019 by ginerjm

[Barand]

@ginerjm is correct. You cannot pass table or column names as parameters to a prepared query; only data values.

It sounds like you need to do something like this (three table example for brevity)

DELETE table1, table2, table3
FROM table1
     JOIN table2 USING (job_id)
     JOIN table3 USING (job_id)
WHERE table1.job_id = ?

 

[ginerjm]

I like Barand's answer.  I wasn't sure about using a join for a Delete query, so his is definitely better.

[Adamhumbug]

12 minutes ago, Barand said:

@ginerjm is correct. You cannot pass table or column names as parameters to a prepared query; only data values.

It sounds like you need to do something like this (three table example for brevity)

DELETE table1, table2, table3
FROM table1
     JOIN table2 USING (job_id)
     JOIN table3 USING (job_id)
WHERE table1.job_id = ?

 

Thanks for this, looks very tidy.

I have

DELETE FROM ssm_job
JOIN ssm_menu_order USING job_id
JOIN ssm_equipment_order USING job_id
JOIN ssm_money_order USING job_id
WHERE ssm_job.job_id = ?

and i am getting the following error

Fatal error: Uncaught mysqli_sql_exception: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'JOIN ssm_menu_order USING job_id JOIN ssm_equipment_order' at line 2 in /homepages/29/d742272110/htdocs/actions/delete-whole-job-action.php:5 Stack trace: #0 /homepages/29/d742272110/htdocs/actions/delete-whole-job-action.php(5): mysqli->prepare('\n ...') #1 {main} thrown in /homepages/29/d742272110/htdocs/actions/delete-whole-job-action.php on line 5

[Adamhumbug]

I thought it was the brackets but when i put the following, i get the same error

 

$stmt = $conn->prepare("
                       DELETE FROM ssm_job
                       JOIN ssm_menu_order USING (job_id)
                       JOIN ssm_equipment_order USING (job_id)
                       JOIN ssm_money_order USING (job_id)
                       WHERE ssm_job.job_id = ?
						");

 

Edited December 16, 2019 by Adamhumbug

[Barand]

Brackets will indeed be an improvement. You also need to specify the tables you want to delete from. That will only delete from ssm_job.

Edited December 16, 2019 by Barand

[Adamhumbug]

Strangely this is running the code and appears to be working correctly but when looking in the Db it is not actually removing anything

$jid = 21;
$stmt = $conn->prepare("
                       DELETE ssm_job, ssm_menu_order, ssm_equipment_order, ssm_money_order FROM ssm_job
                       JOIN ssm_menu_order USING (job_id)
                       JOIN ssm_equipment_order USING (job_id)
                       JOIN ssm_money_order USING (job_id)
                       WHERE ssm_job.job_id = ?
						");
$stmt->bind_param( 'i',$jid );
$stmt->execute();
// echo $stmt -> affected_rows;
$stmt->close();

 

[Barand]

Do all four tables contain a record with job_id = 21?

[Adamhumbug]

3 minutes ago, Barand said:

Do all four tables contain a record with job_id = 21?

Actually, no.

I had assumed ( what do they say about this ) that it would just remove it from the ones that contained it.

[Barand]

As with SELECT statements, INNER JOIN requires matching records.

If some tables may not contain the job_id being deleted then you need LEFT JOINS

DELETE ssm_job, ssm_menu_order, ssm_equipment_order, ssm_money_order 
FROM ssm_job
   LEFT JOIN ssm_menu_order USING (job_id)
   LEFT JOIN ssm_equipment_order USING (job_id)
   LEFT JOIN ssm_money_order USING (job_id)
WHERE ssm_job.job_id = ? 

 

[ginerjm]

Actually, wouldn't all of the tables have to have a column name of job_id?  And - is "FROM" a required field to distinguish column names from table names?

[Barand]

10 minutes ago, ginerjm said:

wouldn't all of the tables have to have a column name of job_id

 

That was established in the question

2 hours ago, Adamhumbug said:

There is going to be 10 or so tables that need to delete *  where $job_id = ?

 

10 minutes ago, ginerjm said:

And - is "FROM" a required field to distinguish column names from table names

???

FROM is part of the DELETE syntax just as it is part of the SELECT syntax and has the same role - defining the table references.

Edited December 16, 2019 by Barand

[Adamhumbug]

53 minutes ago, Barand said:

As with SELECT statements, INNER JOIN requires matching records.

If some tables may not contain the job_id being deleted then you need LEFT JOINS

DELETE ssm_job, ssm_menu_order, ssm_equipment_order, ssm_money_order 
FROM ssm_job
   LEFT JOIN ssm_menu_order USING (job_id)
   LEFT JOIN ssm_equipment_order USING (job_id)
   LEFT JOIN ssm_money_order USING (job_id)
WHERE ssm_job.job_id = ? 

 

Worked Perfectly

[benanamen]

I am surprised there has been no mention of foreign key constraints.

OP, if you set up proper foreign key relationships, all you would have to do is delete the parent record using ON DELETE CASCADE and all the child records would automatically delete as well.

Here is some information about it.

http://www.mysqltutorial.org/mysql-on-delete-cascade/

Edited December 16, 2019 by benanamen

[Adamhumbug]

19 hours ago, benanamen said:

I am surprised there has been no mention of foreign key constraints.

OP, if you set up proper foreign key relationships, all you would have to do is delete the parent record using ON DELETE CASCADE and all the child records would automatically delete as well.

Here is some information about it.

http://www.mysqltutorial.org/mysql-on-delete-cascade/

Thanks for this, ill take a look. 

[Barand]

For example, if you have these tables ... 

CREATE TABLE `test1` (
  `job_id` int(11) NOT NULL AUTO_INCREMENT,
  `test1_descrip` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`job_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
	 
	CREATE TABLE `test2` (
  `test2_id` int(11) NOT NULL AUTO_INCREMENT,
  `job_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`test2_id`),
  KEY `idx_test2_job_id` (`job_id`),
  CONSTRAINT `test2_fk1` 
        FOREIGN KEY (`job_id`) 
        REFERENCES `test1` (`job_id`) 
        ON DELETE CASCADE 
        ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
	 
	CREATE TABLE `test3` (
  `test3_id` int(11) NOT NULL AUTO_INCREMENT,
  `job_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`test3_id`),
  KEY `idx_test3_job_id` (`job_id`),
  CONSTRAINT `test3_fk1` 
        FOREIGN KEY (`job_id`) 
        REFERENCES `test1` (`job_id`) 
        ON DELETE CASCADE 
        ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
	 
CREATE TABLE `test4` (
  `test4_id` int(11) NOT NULL AUTO_INCREMENT,
  `job_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`test4_id`),
  KEY `idx_test4_job_id` (`job_id`),
  CONSTRAINT `test4_fk1` 
        FOREIGN KEY (`job_id`) 
        REFERENCES `test1` (`job_id`) 
        ON DELETE CASCADE 
        ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

... where test1 is the parent table and tables 2. 3 and 4 are the child tables, each with a foreign key referencing the primary key in the first table
 

BEFORE
                            +--------+---------------+
                            | job_id | test1_descrip |
                            +--------+---------------+
                            |     20 | Job 20        |
                            |     21 | Job 21        |
                            |     22 | Job 22        |
                            |     23 | Job 23        |
                            |     24 | Job 24        |
                            |     25 | Job 25        |
                            +--------+---------------+
                                  |
               +------------------+----------+------------------------------+                 
               |                             |                              |
+----------+--------+         +----------+--------+          +----------+--------+
| test2_id | job_id |         | test3_id | job_id |          | test4_id | job_id |
+----------+--------+         +----------+--------+          +----------+--------+
|        8 |     21 |         |        2 |     20 |          |        4 |     21 |
|        7 |     24 |         |        3 |     21 |          |        5 |     22 |
+----------+--------+         |        4 |     22 |          |        6 |     23 |
                              +----------+--------+          |        7 |     24 |
                                                             +----------+--------+

Delete query...

DELETE FROM test1 WHERE job_id = 21;

Records for job 21 also deleted from test2, test3, test4 due to the "ON DELETE CASCADE" settings in the foreign key definitions

AFTER
                            +--------+---------------+
                            | job_id | test1_descrip |
                            +--------+---------------+
                            |     20 | Job 20        |
                            |     22 | Job 22        |
                            |     23 | Job 23        |
                            |     24 | Job 24        |
                            |     25 | Job 25        |
                            +--------+---------------+
                                  |
               +------------------+----------+------------------------------+                 
               |                             |                              |
+----------+--------+       +----------+--------+             +----------+--------+
| test2_id | job_id |       | test3_id | job_id |             | test4_id | job_id |
+----------+--------+       +----------+--------+             +----------+--------+
|        7 |     24 |       |        2 |     20 |             |        5 |     22 |
+----------+--------+       |        4 |     22 |             |        6 |     23 |
                            +----------+--------+             |        7 |     24 |
                                                              +----------+--------+

 

[Adamhumbug]

25 minutes ago, Barand said:

For example, if you have these tables ... 

CREATE TABLE `test1` (
  `job_id` int(11) NOT NULL AUTO_INCREMENT,
  `test1_descrip` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`job_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
	 
	CREATE TABLE `test2` (
  `test2_id` int(11) NOT NULL AUTO_INCREMENT,
  `job_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`test2_id`),
  KEY `idx_test2_job_id` (`job_id`),
  CONSTRAINT `test2_fk1` 
        FOREIGN KEY (`job_id`) 
        REFERENCES `test1` (`job_id`) 
        ON DELETE CASCADE 
        ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
	 
	CREATE TABLE `test3` (
  `test3_id` int(11) NOT NULL AUTO_INCREMENT,
  `job_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`test3_id`),
  KEY `idx_test3_job_id` (`job_id`),
  CONSTRAINT `test3_fk1` 
        FOREIGN KEY (`job_id`) 
        REFERENCES `test1` (`job_id`) 
        ON DELETE CASCADE 
        ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
	 
CREATE TABLE `test4` (
  `test4_id` int(11) NOT NULL AUTO_INCREMENT,
  `job_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`test4_id`),
  KEY `idx_test4_job_id` (`job_id`),
  CONSTRAINT `test4_fk1` 
        FOREIGN KEY (`job_id`) 
        REFERENCES `test1` (`job_id`) 
        ON DELETE CASCADE 
        ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

... where test1 is the parent table and tables 2. 3 and 4 are the child tables, each with a foreign key referencing the primary key in the first table
 

BEFORE
                            +--------+---------------+
                            | job_id | test1_descrip |
                            +--------+---------------+
                            |     20 | Job 20        |
                            |     21 | Job 21        |
                            |     22 | Job 22        |
                            |     23 | Job 23        |
                            |     24 | Job 24        |
                            |     25 | Job 25        |
                            +--------+---------------+
                                  |
               +------------------+----------+------------------------------+                 
               |                             |                              |
+----------+--------+         +----------+--------+          +----------+--------+
| test2_id | job_id |         | test3_id | job_id |          | test4_id | job_id |
+----------+--------+         +----------+--------+          +----------+--------+
|        8 |     21 |         |        2 |     20 |          |        4 |     21 |
|        7 |     24 |         |        3 |     21 |          |        5 |     22 |
+----------+--------+         |        4 |     22 |          |        6 |     23 |
                              +----------+--------+          |        7 |     24 |
                                                             +----------+--------+

 

Delete query...

DELETE FROM test1 WHERE job_id = 21;

 

Records for job 21 also deleted from test2, test3, test4 due to the "ON DELETE CASCADE" settings in the foreign key definitions

AFTER
                            +--------+---------------+
                            | job_id | test1_descrip |
                            +--------+---------------+
                            |     20 | Job 20        |
                            |     22 | Job 22        |
                            |     23 | Job 23        |
                            |     24 | Job 24        |
                            |     25 | Job 25        |
                            +--------+---------------+
                                  |
               +------------------+----------+------------------------------+                 
               |                             |                              |
+----------+--------+       +----------+--------+             +----------+--------+
| test2_id | job_id |       | test3_id | job_id |             | test4_id | job_id |
+----------+--------+       +----------+--------+             +----------+--------+
|        7 |     24 |       |        2 |     20 |             |        5 |     22 |
+----------+--------+       |        4 |     22 |             |        6 |     23 |
                            +----------+--------+             |        7 |     24 |
                                                              +----------+--------+

 

 

I would have to change my database type from MyIsam to innoDB - is there any reason why i shouldnt do that?

[Barand]

InnoDB has many advantages over MyIsam. The time MyIsam comes out top is when you have lots of data retrieval from large databases; then it's faster.