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

Rowland Shaw
– Rowland Shaw

2014-09-15 08:05:58 +00:00
Commented Sep 15, 2014 at 8:05 — Rowland Shaw
– Rowland Shaw, Commented Sep 15, 2014 at 8:05
possible duplicate of Deleting rows with MySQL LEFT JOIN

Dmitriy.Net
– Dmitriy.Net

2014-09-15 08:10:50 +00:00
Commented Sep 15, 2014 at 8:10 — Dmitriy.Net
– Dmitriy.Net, Commented Sep 15, 2014 at 8:10

Dmitriy.Net · Accepted Answer · 2014-09-15 08:08:15Z

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, 2014 at 8:08

Dmitriy.Net

1,52013 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Kapil Bhagchandani · Accepted Answer · 2014-09-15 08:23:16Z

1

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

answered Sep 15, 2014 at 8:23

Kapil Bhagchandani

4914 silver badges17 bronze badges

Comments

Thorsten Kettner · Accepted Answer · 2014-09-15 08:14:57Z

0

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

answered Sep 15, 2014 at 8:14

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

Collectives™ on Stack Overflow

mysql DELETE query with JOIN error...

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related