Solved

MySQL: DELETE / JOIN

Posted on 2011-09-07
5
341 Views
Last Modified: 2012-05-12
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

0
Comment
Question by:hankknight
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 82

Expert Comment

by:hielo
ID: 36497586
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
 
LVL 16

Author Comment

by:hankknight
ID: 36497925
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
 
LVL 82

Expert Comment

by:hielo
ID: 36497999
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
 
LVL 16

Author Comment

by:hankknight
ID: 36498220
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
 
LVL 82

Accepted Solution

by:
hielo earned 500 total points
ID: 36498289
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

Featured Post

Webinar: MongoDB® Index Types

Join Percona’s Senior Technical Services Engineer, Adamo Tonete as he presents “MongoDB Index Types, How, When and Where Should They be Used?” on Wednesday, July 12, 2017 at 11:00 am PDT / 2:00 pm EDT (UTC-7).

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question