mysql DELETE query with JOIN error...

Question

Here is my problem,

I have to build a query in mysql with join statements... my database tables look like this :

table 1: 
    contact, with contact_id, contact_value, contact_relation
table 2 : 
    relation, with relation_id, relation_one, relation_two

I have to delete from contact where contact have relation value (a relation id) AND all of these relations but ONLY where my relation_one is equal to a predefined value...

so the actual idea I have, is this one :

DELETE FROM 'relation' INNER JOIN contact ON contact.contact_relation = relation.relation_id WHERE relation_one = MyValue

But it is NOT doing what I mean...

I try it this way in my mysql:

DELETE relation.* FROM `relation` INNER JOIN `contact` ON `contact`.`contact_relation`=`relation`.`relation_id` WHERE `relation_one` = 48

I assume that this might look like a stupid question... even an "already answered one", BUT i looked through stack, found similar but did not solve my problem because when I apply this query, it deletes ALL relations WHERE relation_one = 48, NOT ONLY the ones I need... (I mean those which are referenced into the contact table under the contact_relation (which is the id of the relations I MAY delete, if these relations have relation_one set at 48 ONLY!)

I mean: I can have 100 records in relation table, where relation_one IS 48... but i only want to delete these "relations" where the relation_id = contact_relation... my contact.contact_relation is null in most cases... but sometimes, for 2/100 it is an existing relation_id written there... these are the relations I "may have to" delete...

It may help to illustrate this with a [SQL Fiddle](http://sqlfiddle.com) — Rowland Shaw, Sep 15 '14 at 08:05
possible duplicate of [Deleting rows with MySQL LEFT JOIN](http://stackoverflow.com/questions/2763206/deleting-rows-with-mysql-left-join) — Dmitriy.Net, Sep 15 '14 at 08:10

score 1 · Answer 1 · answered Sep 15 '14 at 08:08

1

When you deleting using left join, specify the table to remove and not columns:

DELETE `relation` FROM `relation` 
   INNER JOIN `contact` ON `contact`.`contact_relation`=`relation`.`relation_id` 
   WHERE `relation_one` = 48

answered Sep 15 '14 at 08:08

Dmitriy.Net

1,476
13
24

score 1 · Answer 2 · answered Sep 15 '14 at 08:23

based on your question and query sample I assume you are trying to delete associated data from relation table which is associated with contact table and having relation_one value 48 this can be done as :

DELETE FROM `relation`, `contact` USING `relation` INNER JOIN `contact`
WHERE `relation`.`relation_id`=`contact`.`contact_relation` AND `relation_one`=48;

for more refer Multi-Table Deletes on following url : http://dev.mysql.com/doc/refman/5.6/en/delete.html

score 0 · Answer 3 · answered Sep 15 '14 at 08:14

If I understand correctly, you want to delete all relation records with relation_one = 48 and their id in contact.contact_relation. This would simply be:

delete from relation
where relation_one = 48
and relation_id in (select contact_relation from contact);

mysql DELETE query with JOIN error...

3 Answers3