CODEWITHSUNDEEP
mysqlsql
Navid .
Navid .

Reputation: 107

MySQL Query (change SELECT to DELETE)

I have this mysql query:

SELECT 
    F.ID,
    F.REF1,
    F.REF2
FROM FRIENDS F
LEFT JOIN ACCOUNTS A1 on A1.id = F.REF1
LEFT JOIN ACCOUNTS A2 on A2.id = F.REF2
WHERE A1.WEB_IP = A2.WEB_IP;

I'm trying to change it to a DELETE command instead of SELECT. Somehow its now working.

Query which does not work:

DELETE FROM FRIENDS WHERE LEFT JOIN ACCOUNTS A1 on A1.id = FRIENDS.REF1
LEFT JOIN ACCOUNTS A2 on A2.id = FRIENDS.REF2
WHERE A1.WEB_IP = A2.WEB_IP;

Upvotes: 2

Views: 160

Answers (5)

Joël Salamin
Joël Salamin

Reputation: 3576

Here is a simple way to delete the rows corresponding to ids returned by a query:

DELETE FROM FRIENDS
WHERE ID IN (SELECT F.ID FROM FRIENDS F LEFT JOIN ...)

Here is a second solution that is more readable and more performant (use of jointure directly from the DELETE statement instead of using a subquery):

DELETE F
FROM FRIENDS F
INNER JOIN ACCOUNTS A1 ON A1.id = F.REF1
INNER JOIN ACCOUNTS A2 ON A2.id = F.REF2
WHERE A1.WEB_IP = A2.WEB_IP

This second proposition use INNER JOIN instead of LEFT JOIN to optimize the query. You're looking for friends that are linked by REF1 and REF2 with the same WEB_IP. The use of LEFT JOIN is useless in this situation.

Hope this will help.

Upvotes: 2

ErstwhileIII
ErstwhileIII

Reputation: 4843

You may want to use:

DELETE friends
FROM frineds 
     LEFT JOIN accounts a1 on a1.id = friends.ref1
     LEFT JOIN accounts a2 on a2.id = friends.ref2
WHERE a1.web_ip = a2.web_ip;

You may find this reference useful

Upvotes: 2

John Ruddell
John Ruddell

Reputation: 25842

you can use that exact select in a subquery for your delete statement

DELETE FROM `friends` 
WHERE `ID` IN 
(   SELECT `ID`
    FROM `FRIENDS` F
    LEFT JOIN `ACCOUNTS` A1 on A1.`id` = F.`REF1`
    LEFT JOIN `ACCOUNTS` A2 on A2.`id` = F.`REF2`
    WHERE A1.`WEB_IP` = A2.`WEB_IP`;
)

Upvotes: 0

Mike Brant
Mike Brant

Reputation: 71384

I personally prefer to leave out the FROM when specifying the tables in the join you are going to delete from. To me this makes it easier to not confuse the traget tables for deletion with the join. IN your case that would look like:

DELETE friends, a1 /* leave off a1 if you only want to delete from friends */
FROM friends
LEFT JOIN accounts as a1
  ON a1.id = friends.ref1
LEFT JOIN accounts as a2
  ON a2.id = friends.ref2
WHERE a1.web_ip = a2.web_ip;

You might also consider getting in the (IMO) good habit of putting MySQL keywords in uppercase and schema objects in lower case. I certainly helps make the query easier to read.

Upvotes: 1

Andomar
Andomar

Reputation: 238048

You can use the "double from" syntax:

delete from f
from friends f
left join accounts a1 on a1.id = f.ref1
...

Upvotes: 1

Related Questions