SQL Query

Hello all I have a quick question with a query. I would like to use this query in Query Analyzer.

I have a table named account. Within account table there is a field named ssnum. I would like to query the account table for any record within it that has account >2 (any account that has more than 1 account) AND has ssnum with a NULL value. So essentially i would like to delete any account within the accounts table that has >1 account and has a null value in ssnum field. Can anyone help me out with that?

Not sure what you mean my more than one account because you list only 1 field in your table -- without something else to key on, how do you know you have a duplicate account?

Ok well this is what i use currently.

select count (*), account from accounts group by account having count (*) > 1

So basically it returns all the accounts in the account table with more than 1 account. I want to delete One of those accounts the one with ssnum having a NULL Value.

Got it, I was missing the idea that you had an account column in the accounts table -- confuzing no?

Try something like the following: WARNING: Back up your data first -- Back up your data first -- Back up your data first (get the idea -- I'd try this on a copy of the table first)

I know this doesnt work but it gives you an example of what im trying to do


DELETE *, account as Expr1
From Accounts
WHERE (select count (*), account from accounts group by account having count (*) > 1) and (ssnum = <NULL>)

Excelent i will give that a try!!!

It worked! Wolffy you are God amoung Gods my friend, you just saved me hours. Appreciated.