+ 1

Subquery here failing:( WHY?!

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 :)

1st Jul 2016, 5:01 PM
Krishna Limani
Krishna Limani - avatar
4 odpowiedzi
+ 3
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
1st Jul 2016, 8:46 PM
Vedant Patadia
Vedant Patadia - avatar
+ 2
in sql doc it is mentioned that you cant delete a record in table using subquery from same table
1st Jul 2016, 6:22 PM
ashu
+ 1
please post your error which is coming. also try to run your subquery alone to see what it is returning
1st Jul 2016, 5:14 PM
ashu
+ 1
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
1st Jul 2016, 5:53 PM
Krishna Limani
Krishna Limani - avatar