MySQL: DELETE / JOIN

I want to delete all rows from `Group_Members` where `GroupID` equals  '27' and its `UserSuspended` value from the `Member` table, joined on `ID` equals  '1'
DELETE FROM `Group_Members` JOIN `Member` ON `ID` WHERE `Member.UserSuspended` = '1' and `Group_Members.GroupID` = '27'

Open in new window

LVL 16
hankknightAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
hieloConnect With a Mentor Commented:
What part of "list all the tables involved first" did you not understand?

DELETE FROM `Group_Members` INNER JOIN...

is NOT the same as:

DELETE FROM `Group_Members`,`Member` INNER JOIN...


I even wrote the syntax for you! Try copy and paste.
0
 
hieloCommented:
ON `ID`=?WHAT? (what's the "common/joining column)

Also, you need the backticks around the TABLE AND Field name when using table.field.

WRONG:
`Group_Members.GroupID`

Correct:
`Group_Members`.`GroupID`


Back up you db first and then Try:
DELETE FROM `Group_Members` JOIN `Member` ON `Member`.`ID`=`Group_Members`.`GroupID` WHERE `Member`.`UserSuspended` = '1' and `Group_Members`.`GroupID` = '27'

0
 
hankknightAuthor Commented:
Thanks, but I get an error:

Error: 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 'JOIN `Member` ON `Member`.`ID`=`Group_Members`.`GroupID` WHERE `Member`.`Use' at line 2

Any idea how I could figure out what is wrong?
DELETE FROM `Group_Members` 
JOIN `Member` 
ON `Member`.`ID`=`Group_Members`.`GroupID` 
WHERE `Member`.`UserSuspended` = '1' 
AND `Group_Members`.`GroupID` = '22'

Open in new window

0
 
hieloCommented:
list all the tables involved first, then do an inner join

DELETE FROM `Group_Members`,`Member`
INNER JOIN `Member`  
ON `Member`.`ID`=`Group_Members`.`GroupID`
WHERE `Member`.`UserSuspended` = '1'
AND `Group_Members`.`GroupID` = '22'

Also, if UserSuspended and GroupId are defined as numeric types on your db, get rid of the apostrophes around the values.
0
 
hankknightAuthor Commented:
This displays the records I want removed:
SELECT * 
FROM `Group_Members`
INNER JOIN `Member`
ON `Member`.`ID` = `Group_Members`.`MemberID`
WHERE `Group_Members`.`GroupID` = '22' AND `Member`.`UserSuspended` = '1'

Open in new window


However this gives an error:

DELETE
FROM `Group_Members`
INNER JOIN `Member`
ON `Member`.`ID` = `Group_Members`.`MemberID`
WHERE `Group_Members`.`GroupID` = '22' AND `Member`.`UserSuspended` = '1'

Open in new window


Error: 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 'INNER JOIN `Member` ON `Member`.`ID` = `Group_Members`.`MemberID` WHERE `Group' at line 3


Is the problem with "DELETE FROM"?
0
All Courses

From novice to tech pro — start learning today.