Re: SQL question

"chani" <chanakam2000_at_gmail.com> wrote in message news:1145851646.749577.165250_at_i40g2000cwc.googlegroups.com...
> HI everyone!
>
> I have 3 tables
>
> 1. customer (customer_id,name,address) primary key = customer_id
> 2. account (account_number,balance) primary key =account_number
> 3. depositor (account_number,customer_id,deposite_date)
>
> depositor is many to many relation between customer & account
>
> Now i want to delete customers who has account balance less than
> 500.00.
>
> I tried following SQL. It work nice if there is only one customer
> satisfied the condition.
> If more than one customer has account balance less than 500 this didn't
> work.
> It says single row sub query returns multiple row.
>
> here is my sql code
>
> delete from customer
> where
> customer_id=
> (
> select
> distinct customer_id
> from
> customer c join depositor d using(customer_id) join account a
> using(account_number)
> where
> a.balance<500
> );
>
> Please help me.
> What should i do?
>

Chani,

Use the 'IN' operator instead of the '=' because you are dealing with sets. That is, '... WHERE customer_id IN ( ...'.

Douglas Received on Sun Apr 23 2006 - 23:51:05 CDT