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?
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!
DELETE FROM contact, contact_additional USING contact, contact_additional WHERE contact.ticket=contact_additional.ticket AND contact.ticket=`mm1234.1234`;
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...
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.
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!);
DELETE FROM contact, contact_additional
USING contact, contact_additional
WHERE (contact.ticket=contact_additional.ticket AND contact.ticket='mm1234.1234')