Why doesn't my MySQL query work?

Question

Why doesn't my MySQL query work?

---

Query:

DELETE FROM jos_community_awards a
LEFT JOIN jos_community_users u
ON a.userId = u.userid WHERE a.points > u.points;

---

Error:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that >corresponds to your MySQL server version for the right syntax to use near 'a LEFT JOIN jos_community_users u ON a.userId = u.userid WHERE a.points > u.poi' at line 1

Answer 1

It would appear you can't delete from an alias. Or you need to specify the entire row so a.* I think you need to specify the entire row with .* either on the table name or the alias.

Answer 2

DELETE queries with joins are bit tricky. Adding the table name after the DELETE keyword should help if I remember correctly:

DELETE jos_community_awards FROM jos_community_awards ...