Jump to content

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.

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

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

 

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

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

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


 
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
Link to post
Share on other sites
This thread is more than a year old.

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.