• We’re currently investigating an issue related to the forum theme and styling that is impacting page layout and visual formatting. The problem has been identified, and we are actively working on a resolution. There is no impact to user data or functionality, this is strictly a front-end display issue. We’ll post an update once the fix has been deployed. Thanks for your patience while we get this sorted.

MySQL question...multi table deletes.

E

Entity

Lifer
Ok, I have two tables that I'm working with: tblUser and tblUserRegion.

tblUser has userID, userEmail, userAddress.

tblUserRegion has userRegionID (PK), userID and userRegion. it has a 1:n ration between userID and userRegion...there is a separate entry for each region a user is part of.

Using foreign keys is unfortunately not an option with this MySQL setup. So: when I delete a user from tblUser, I'd like to make sure that all mentionings of them get deleted from tblUserRegion as well.

Trying something like this:

DELETE tblUsers, tblUserRegions FROM tblUsers, tblUserRegions WHERE tblUsers.UserEmail="email@email.com" AND tblUsers.UserID=tblUserRegions.UserID

But it doesn't seem to be right. Any advice?

thanks,
Rob
 
Why not try to run the two queries individually. For example:

-------------------------------------------
DELETE
FROM tblUserRegions
WHERE UserID = "Your User ID"
-------------------------------------------
DELETE
FROM tblUsers
WHERE UserID = "Your User ID"
-------------------------------------------
 
Originally posted by: webie
Why not try to run the two queries individually. For example:

-------------------------------------------
DELETE
FROM tblUserRegions
WHERE UserID = "Your User ID"
-------------------------------------------
DELETE
FROM tblUsers
WHERE UserID = "Your User ID"
-------------------------------------------
Click to expand...

I won't be deleting by userID, though; I'll be deleting by email (for various reasons). That's why it'd make it easier to run one query: beyond that, I'd just like to learn how I should do this properly for future reference. I've read up in the section on DuBois where it talks about this, but I can't get it to work.

Rob
 
I just tried this on a similiar situation I have - a customers table with customer_id and email, and a customers_address table with customers_id, same 1:many relationship. The following succesfully deleted the one row from customers and 3 rows from customers_address:

DELETE customers,customers_address FROM customers,customers_address WHERE customers_address.customer_id=customers.customer_id AND customers.email='me';

It doesn't look different then yours, what's the problem you're having?
 
DELETE tblUsers.*, tblUserRegions.* FROM tblUsers
INNER JOIN tblUserRegions on tblUserRegions.UserID = tblUsers.UserID
WHERE tblUsers.UserEmail="email@email.com";
 
Still not working.

Attached is my table code:

Tried this query with it:

DELETE tblAgents.*, tblAgentRegions.* FROM tblAgents INNER JOIN tblAgentRegions on tblAgentRegions.agentID=tblAgents.agentID WHERE tblAgents.agentEmail="robcole@u.washington.edu"
 
Here's the error message I get 🙁

You have an error in your SQL syntax near 'tblAgents.*, tblAgentRegions.* FROM tblAgents INNER JOIN tblAgentRegions on tblA' at line 1
 
Check your version of mysql, or that you have the correct permissions. I copied and pasted the code and it worked just fine 😕
 
I don't really understand your SQL syntax...

DELETE tblUsers, tblUserRegions FROM tblUsers, tblUserRegions WHERE tblUsers.UserEmail="email@email.com" AND tblUsers.UserID=tblUserRegions.UserID

Delete the tables from the tables?


Wouldn't it just be:
DELETE FROM tblUsers, tblUserRegions WHERE tblUsers.UserEmail="email@email.com" AND tblUsers.UserID=tblUserRegions.UserID
 
Thanks for the help, guys.

The simplest answer is usually the best. It turns out that MySQL 3 is standard on Fedora, and I had to upgrade.

Upgraded now, and the query I started with works fine. 😛

Rob
 
You must log in or register to reply here.

TRENDING THREADS

Back
Top