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;
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
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.
You can use the "double from" syntax:
delete from f
from friends f
left join accounts a1 on a1.id = f.ref1
...
from after delete. Both versions work fine.
If you declare an alias for a table, you must use the alias when referring to the table: and then the syntax shows this query should work.. I would remove the first from just because its slightly confusing to read +1 :)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.
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`;
)
