MySQL question...multi table deletes.

Toggle sidebar Toggle sidebar
E

Entity

Lifer
Oct 11, 1999
10,090
0
0
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
 
W

webie

Member
Mar 23, 2004
36
0
0
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"
-------------------------------------------
 
E

Entity

Lifer
Oct 11, 1999
10,090
0
0
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
 
T

Templeton

Senior member
Oct 9, 1999
467
0
0
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?
 
B

Beau

Lifer
Jun 25, 2001
17,730
0
76
www.beauscott.com
DELETE tblUsers.*, tblUserRegions.* FROM tblUsers
INNER JOIN tblUserRegions on tblUserRegions.UserID = tblUsers.UserID
WHERE tblUsers.UserEmail="email@email.com";
 
E

Entity

Lifer
Oct 11, 1999
10,090
0
0
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"
 
E

Entity

Lifer
Oct 11, 1999
10,090
0
0
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
 
I

igowerf

Diamond Member
Jun 27, 2000
7,697
1
76
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
 
E

Entity

Lifer
Oct 11, 1999
10,090
0
0
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. :p

Rob
 
E

eklass

Golden Member
Mar 19, 2001
1,218
0
0
just a quick note, mysql has a table type called InnoDB that supports foreign keys
 
You must log in or register to reply here.

TRENDING THREADS

COMPANY
RESOURCES
FOLLOW
Top Bottom