June 9, 2010 at 9:57 am
So, what I'm trying to do is delete everything from one table, where it doesn't exist in another table. I wrote this query:
delete from users
where userid =
(select users.userID from users
left join aspnet_users
on users.Username = aspnet_users.UserName
where aspnet_users.UserName is null)
I thought that would give me what I want, but I get this message:
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
Any ideas?
Jordon
June 9, 2010 at 10:06 am
chage the where cluase from 'userid=' to 'userid in'
delete from users
where userid in
(select users.userID from users
left join aspnet_users
on users.Username = aspnet_users.UserName
where aspnet_users.UserName is null)
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
June 9, 2010 at 10:08 am
delete from users
where userid in
(select users.userID from users
left join aspnet_users
on users.username = aspnet_users.username
where aspnet_users.UserName is null)
Change "userid =" to "userid in"
June 9, 2010 at 10:09 am
Thanks! That worked perfectly. I knew that I was missing something!
Gotta remember that IN statement!
Jordon
June 9, 2010 at 10:12 am
Or, to simplify, you could rewrite it like below:
delete from users
where userid NOT IN (
SELECT userid FROM dbo.aspnet_users)
or
delete from users
where NOT EXISTS
(SELECT 1 FROM aspnet_users WHERE aspnet_users.userID =users.userID)
June 9, 2010 at 10:50 am
Howard is correct. Given the simplicity of the question at hand (no offense Jordan) I didn't want to potentially confuse him.
Using NOT EXISTS is a better solution considering that it can usse an index in the subquery where NOT IN has to do nested full table scans. That said, using NOT IN can be more efficient or just as efficient as using NOT EXISTS if the subquery is proven to not contain NULL values.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply