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 profile.name=users.username 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 profile.name 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 users.id=profile.user_id WHERE users.username !=‘admin’
i realize the manual isn’t as clear as it could be
[COLOR="Blue"]DELETE profile
FROM[/COLOR] users
INNER
JOIN profile
ON profile.user_id = users.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 
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
LEFT JOIN users
ON profile.user_id = users.id
WHERE users.id IS NULL
Looks like you helped someone with a similar problem back in 2006:
old doesn’t always mean out of date, eh 
Rudy - you’re freaking awesome
Oh, and I love your book, too, though as you might have seen me post - I’ve been rather jumping the chapters.
Best,
Greg
greg, thanks for the very kind words, you’ve made my day 