• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 604
  • Last Modified:

Cross-Table Delete

Simplified situation:  I have a table that I want to delete entries from based on entries in another table:

Table1
----------
user
otherdata

Table2
----------
user
group

My query so far:
DELETE FROM Table1 USING Table2 WHERE Table1.user = Table2.user AND Table2.group = 'something I know';

Not sure if this is the right query, but this is where I am right now.  Currently, it's giving me the error:
ERROR 1066: Not unique table/alias: 'Table2'

However, 'Table2' is unique as far as I can see.. no other tables, or fields are named that.
0
Chireru
Asked:
Chireru
  • 3
1 Solution
 
jdlambert1Commented:
You need a reference to table 1 in the USING clause so it can join:

DELETE FROM Table1 USING Table1, Table2 WHERE Table1.user = Table2.user AND Table2.group = 'something I know';
0
 
jdlambert1Commented:
You can also do it this way:

DELETE Table1 FROM Table1, Table2 WHERE Table1.user = Table2.user AND Table2.group = 'something I know';
0
 
ChireruAuthor Commented:
Yup, that tiny change did it, thanks :)
0
 
jdlambert1Commented:
>tiny change did it
I once spent all day trying to figure out an error, and finally discovered a missing period was the only problem...
 
0

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now