Solved

MySQL: DELETE / JOIN

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MYSQL/PHP inserting 1969-12-31 instead of NULL 12 49
Bubble user-defined Sql RAISERROR(...) to c# exception 14 171
Selecting specific rows 3 54
showing numeric numbers 2 38
Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

825 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