MySQL syntax for table join on delete

delayedinsanity · 04-03-2008, 03:16 AM

I'm having trouble with the syntax for this statement. When I delete a row from my contact table, I need to also delete any corresponding rows from my contact_additional table. Instead of doing two queries I was trying to do a join based on the syntax I'm reading in the manual, but it's not working;

DELETE FROM contact, contact_additional WHERE contact.ticket=`MM1234.1234` AND contact_additional.ticket=`MM1234.1234`

I keep getting errors! I tried a bunch of variations but I can't figure it out yet.

Also, I'm wondering if I should bother trying to do a join here, because if there's no data in the contact_additional table that matches, I'm guessing it won't delete either row?
-m

Orc · 04-03-2008, 06:53 AM

Shouldn't this be in MySql forum? Also I'll help:

Code:

DELETE FROM `table` WHERE `column` = `row`

Correct? or what? I mean if you have two of the same rows in two tables and they're both used as the same, then you should do the same query only delete it from that other table

serversphere · 04-03-2008, 01:08 PM

No idea if this will work (not sure you can actually run a join query against a table that is being edited) but try this:

Code:

DELETE FROM contact AS c, ca
LEFT JOIN contact_additional AS ca 
ON c.ticket=ca.ticket
WHERE c.ticket='MM1234.1234'

delayedinsanity · 04-03-2008, 02:42 PM

I think I'm just going to stick with two queries, because I need it to delete from the first table regardless of there being additional information in the second, but I want to figure this out just because I can't!

I tried yours serversphere and I got an error 1064 (42000) from MySQL. So I tried this, based off an example in the MySQL documentation at MySQL :: MySQL 5.0 Reference Manual :: 12.2.1 DELETE Syntax, but...

Code:

DELETE FROM contact, contact_additional USING contact, contact_additional WHERE contact.ticket=contact_additional.ticket AND contact.ticket=`mm1234.1234`;

and get...

Code:

ERROR 1054 (42S22): Unknown column 'mm1234.1234' in 'where clause'

So I'm not sure how to tell it what information to look for in the delete. I don't want it to just delete all columns that match up, that's just bad. The more I read the manual here, it looks like I may have to do a SELECT first...

Code:

CREATE TEMPORARY TABLE tmptable
SELECT t1.* FROM table1 as t1
LEFT JOIN table2 as t2 ON t2.id = t1.id
WHERE t2.id is NULL;

DELETE FROM table1 USING tmptable, table1
WHERE table.id = tmptable.id;

...anyways... Sorry about this being posted in the wrong forum, I've honestly never scrolled that far down before to realize there was one! I'm new (obviously) so I've just been catching up reading the beginner/advanced and general forums. If a mod wants to move it, I'll know where to find it! Thanks for all the help guys.
-m

wGEric · 04-03-2008, 04:57 PM

Quote:

Originally Posted by delayedinsanity

I tried yours serversphere and I got an error 1064 (42000) from MySQL. So I tried this, based off an example in the MySQL documentation at MySQL :: MySQL 5.0 Reference Manual :: 12.2.1 DELETE Syntax, but...

Code:

DELETE FROM contact, contact_additional USING contact, contact_additional WHERE contact.ticket=contact_additional.ticket AND contact.ticket=`mm1234.1234`;

and get...

Code:

ERROR 1054 (42S22): Unknown column 'mm1234.1234' in 'where clause'

You put backticks around mm1234.1234 so it thinks that is a column and not a string. I think you want something like this.

sql Code:

DELETE FROM contact, contact_additional
USING contact, contact_additional
WHERE contact.ticket = contact_additional.ticket
AND contact.ticket = 'mm1234.1234';

That will delete where contact.ticket's value is mm1234.1234.

delayedinsanity · 04-03-2008, 05:39 PM

Now I feel silly. Thanks Eric, that worked. I tried this query based on that to try and delete rows from each table, or just a row from the contact table if no match was found in the additional table, and I wound up deleting ALL the records in the additional table (argh!);

Code:

DELETE FROM contact, contact_additional
USING contact, contact_additional
WHERE (contact.ticket=contact_additional.ticket AND contact.ticket='mm1234.1234')
OR contact.ticket='mm1234.1234';