Solved

MySQL: DELETE / JOIN

Posted on 2011-09-07
5
334 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MySQL Grouping 2 54
mysql date time 14 43
Import MySQL data into MS Access using VB.Net interface 5 52
reverse engineer .sql from php files 11 53
More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

733 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