Delete Using Join

I’m trying to delete all fields in a table called profile where my fk to a related table called users does not exist.

My query:

DELETE FROM profile INNER JOIN users on where users.username !=admin

we have a MySQL forum, and a Databases forum for every other database besides MySQL

since you posted in the Databases forum, could you tell us which database you are using?

the joined delete syntax varies, and depends on which one it is

Sorry, this question would’ve have been more appropriately placed in the mysql forum. But yes, I am using mysql. Thank you for any answers.

okay, no prob

you wrote

users.username !=admin

is admin a column?

if it’s supposed to be a string value, it should be in quotes

in any case, the joined delete syntax is a bit different from the single table delete syntax, the FROM keyword goes in a different spot…

but i can’t fix it until you clear up what “admin” means

admin is a string. I forgot the quotes

and the delete query, as you wrote it, doesn’t work, correct?

No, this syntax isn’t working for me.

I did find a different error in my query though. Column was a column I added after the db had begun being populated so the admin account didn’t have a value for the profile name field.

So instead I’ve tried the join on the id field. Though I’m still generating a syntax error:

DELETE FROM profile INNER JOIN users ON WHERE users.username !=‘admin’

i realize the manual isn’t as clear as it could be

[COLOR="Blue"]DELETE profile 
  FROM[/COLOR] users 
  JOIN profile 
    ON profile.user_id =
 WHERE users.username <> 'admin'

as with all code that might change the content of your tables, make sure you take a backup first before running this

Well the query is running successfully now. But it’s not deleting any records.

I created a test account and was able to delete it by targeting it directly with an equality check

WHERE users.username = ‘testdelete’

rather than delete everything not equal with inequality check we’ve been using

WHERE users.username <> ‘testdelete’

I’ve also tried the != operator with no success.

If memory serves, I’ve read certain joins may not return the expected matches, so I’ve tried inner, outer, right and left. But no luck here either.

it’s gotta be a data problem :slight_smile:

your problem can easily be resolved if you would kindly take the time to prepare a test case – a few rows of test data in ~both~ tables – and then dump the tables so that we get both the CREATE TABLE and the INSERT statements so that we can test things ourselves

Alright, I think I know what the problem is. I’d already cleared the users table except for the admin row.

I thought I’d already tested to rule this out as our problem.

I guess we can’t run an equality check against records that don’t exist.

So this would bring me to my next question, how to remove orphaned data that doesn’t have any records in a related table?

could you please be a bit more specific? which tables? how are they related?

Each time a user registers that have their data entered into both the users table and the profile table. There’s an fk of user_id in the profile table that relates to the user’s id in the users table.

Being I’d already cleared my users table except for my admin account I couldn’t delete rows in the profile based on our inequality check:

WHERE users.username <> ‘admin’

Or at least this my guess as to why the query wasn’t working… Perhaps there’s a better explanation.

After learning the flaw in my logic I found the EXIST/NOT EXIST operators. While researching these I found a perhaps even simpler method to delete the old data in the profile table:

DELETE profile FROM profile
ON profile.user_id =

Looks like you helped someone with a similar problem back in 2006:

old doesn’t always mean out of date, eh :wink:

Rudy - you’re freaking awesome :slight_smile:

Oh, and I love your book, too, though as you might have seen me post - I’ve been rather jumping the chapters.


greg, thanks for the very kind words, you’ve made my day :smiley: