Solved

MySQL: DELETE / JOIN

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Format data and display in formatted manner 3 55
Database Design Concept 3 51
get the data all row not only one row php 4 51
Create a Select Query and Populate a Table 3 44
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

910 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now