deleting users with no orders

sfullman

I think I can make this question generic.

I have two tables relative to this question, one named users and one named orders.

Orders has a foreign key users_id in it which points back to the users table.

I want to (hopefully in one query) delete all users who do not have an order in the orders table.

I have mysql4.0 I believe and this query doesn't work. Any suggestions?

"DELETE FROM
users LEFT JOIN orders
ON users.id = orders.users_ID
WHERE
orders.id IS NULL"

Sam

dhodge

DELETE FROM users WHERE id NOT IN (SELECT user_ID FROM orders)

Drakla

They're using mysql 4.0 so can't use subqueries. You're just using the wrong variable in your is null bit

DELETE FROM
users LEFT JOIN orders
ON users.id = orders.users_ID
WHERE
orders.users_ID IS NULL

mrhappiness

try

DELETE users
FROM users 
  LEFT JOIN orders ON users.id = orders.users_ID
WHERE orders.users_ID IS NULL

@
it should make no differen whether he compares orders.users_ID or orders.id. if there is no match in the table "orders" all of orders' columns should be NULL

Drakla

Thank you Obi-Wan, I was being a complete plank, I had the query wrong too - this is the right way:

DELETE FROM users
USING users
LEFT JOIN orders
ON users.id = orders.users_ID
WHERE orders.id IS NULL