Delete Many to One

[aaron_karp]

I have 3 tables and I need to delete some rows from them. Table2 has a many-to-one relationship with Table1 and Table 3 has a many-to one relationship with Table2. They look like this:

Table1

ServiceId (pk)

Table2

ServiceId (fk)
ElementId (pk)

Table3

ElementId (fk)

Of course I've left out all the non-key data. So now I need to delete all of the rows in all three tables that correspond to a particular Service ID.

It's simple enough to run something like DELETE FROM Table WHERE ServiceId = $servId

Where $servId is a PHP variable containing the ID of the service I want to delete.

But for Table3, there's no service ID so I can't just delete rows. Basically I want to delete all rows in Table3 where there is a corresponding ElementId in Table2

My question is, would something like this work? And if not, how should I do it?

DELETE FROM Table3 WHERE Table3.ElementId=Table2.ElementId AND Table2.ServiceId=$servId

Any thoughts? Thanks.

[tsinka]

Hi,

MySQL supports DELETE along with any type of join so try this:

DELETE FROM table1 AS t1 LEFT JOIN table2 AS t2
ON t1.ServiceId=t2.ServiceId
LEFT JOIN table3 AS t3
ON t2.ElementId=t3.ElementId
WHERE t1.ServiceId=$servId

But test it on a test database first ...

Thomas

[aaron_karp]

Thanks Thomas. That looks promising. But I'm getting an error. Here's my query and error:

Table1 -> RequestForms
Table2 -> FormElements
Table3 -> FormElementParameters

$sql = "DELETE FROM RequestForms 
	LEFT JOIN FormElements 
	ON RequestForms.ServiceId = FormElements.ServiceId 
	LEFT JOIN FormElementParameters 
	ON FormElements.ElementId = FormElementParameters.ElementId 
	WHERE RequestForms.ServiceId=$form_to_delete";

And the error:

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 'LEFT JOIN FormElements ON RequestForms.ServiceId = F

Any ideas why it's failing?

[tsinka]

which MySQL version do you use ?

[aaron_karp]

Client API version 3.23.57

according to phpinfo()

Is that the number we're looking for?

[tsinka]

Looks a little bit too old, you can use multi table deletes as of MySQL 4.x.

The client version doesn't match the server version in many cases but this is a hint that the server version is some 3.x version.

In this case you might need to split the queries up into different queries.

You can use the mysql_get_server_info() function to get the server version. Just connect to the server and use the connection resource variable as function argument.

Thomas

[aaron_karp]

Alright - guess I'll have to settle for multiple queries. But is there a better way than looping through a SELECT on Table2 and running a DELETE on Table3 for every ElementId that matches?

[tsinka]

Yes,

you can do it like this:

$sql = "SELECT ElementId FROM table2 WHERE ServiceId=$servId";
$res = mysql_query($sql);

$arrElements = array();
while ($row = mysql_fetch_array($res)) {
  $arrElements[] = $row['ElementId'];
}

$elements = implode(",",$arrElements);

$sql = "DELETE FROM table3 WHERE ElementId IN (".$elements.")";

instead of looping throught the result set and executing a delete query on table3 for each row of table2.

Thomas

[aaron_karp]

Neat 🙂

Thanks for all your help with this.