Subquery here failing:( WHY?!

Question

Say I got a "users" table, with columns as UserID, FirstName, LastName, City. Now, suppose there's just a single record/row in this table, with UserID=1. Then, the following SQL query should give back to us just the list of UserIDs in the table which are not equal to 6, namely UserID equal to just 1.

SELECT UserID FROM users WHERE UserID != 6

This definitely gives us back just UserID=1, which is actually the only UserID in the original table anyway.

Now, why is it that the following query fails to delete the one and only record in the original table with UserID=1 ???

DELETE FROM users

WHERE UserID = (SELECT UserID FROM users WHERE UserID != 6);

Just trying to play around tbh and get used to actual practice of SQL queries, subqueries specifically here. BIG THANKS :D for any help :)

Answer 1

firstly , you can do that more simply by removing the subquery..just directly write it
secondly, you cannot delete something when you are actually accessing it's data stream...that's what the error is saying where you are deleting as well as accessing the table simultaneously

Answer 2

in sql doc it is mentioned that you cant delete a record in table using subquery from same table

Answer 3

please post your error which is coming. also try to run your subquery alone to see what it is returning

Answer 4

users table itself:
https://app.box.com/s/vxme3pvk3pkj2s3dn3yidi28on3z1umj

Subquery itself/alone performed works fine:
https://app.box.com/s/6fgwmq0mvw22y9smp4lmyk2sjahbyjum

FAILURE of entire query that has a subquery inside it:
https://app.box.com/s/ez4grf82lytbebxpdi9jnco2361duzp9