Jump to content
Adamhumbug

Delete from multiple tables at once

Recommended Posts

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.

Share this post


Link to post
Share on other sites

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 by ginerjm

Share this post


Link to post
Share on other sites

@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 = ?

 

  • Great Answer 1

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites

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 by Adamhumbug

Share this post


Link to post
Share on other sites

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 by Barand

Share this post


Link to post
Share on other sites

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();

 

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

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 = ? 

 

  • Great Answer 1

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites
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 by Barand

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites

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 by benanamen

Share this post


Link to post
Share on other sites
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. 

Share this post


Link to post
Share on other sites

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 |
                                                              +----------+--------+


 

Share this post


Link to post
Share on other sites
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?

  • Like 1

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.