Hi,

this won't work, because the subquery may only return one column:

DELETE FROM customers WHERE customer_id IN (SELECT customer_id,
SUM(accounting.amount) AS balance FROM customers JOIN accounting USING
(customer_id) HAVING balance > customers.limit)

(Delete all customers with balance > limit.)

How can I do it?

Regards,
André

André Hänsel wrote:
> Hi,
>
> this won't work, because the subquery may only return one column:
>
> DELETE FROM customers WHERE customer_id IN (SELECT customer_id,
> SUM(accounting.amount) AS balance FROM customers JOIN accounting USING
> (customer_id) HAVING balance > customers.limit)
>
> (Delete all customers with balance > limit.)
>
> How can I do it?
>
> Regards,
> André

I don't think that is your only problem. You are not grouping by anything,
so your SUM() will fail.

Also, you should be doing this using the Multiple-table syntax with a join,
rather than a sub-query.

On 29 Mai, 00:33, "Paul Lautman" <paul.laut...@btinternet.com> wrote:
>
> I don't think that is your only problem. You are not grouping by anything,
> so your SUM() will fail.

I deleted it when copying to usenet, of course I had:

DELETE FROM customers WHERE customer_id IN (SELECT customer_id,
SUM(accounting.amount) AS balance FROM customers JOIN accounting USING
(customer_id) GROUP BY order_id HAVING balance > customers.limit)

> Also, you should be doing this using the Multiple-table syntax with a join,
> rather than a sub-query.

Some years ago you suggested a join when I asked a similar question.
But because I like subqueries for readability I thought I could fool
you with the DELETE.

On 28 May, 22:54, André Hänsel <an...@webkr.de> wrote:
> On 29 Mai, 00:33, "Paul Lautman" <paul.laut...@btinternet.com> wrote:
>
>
>
> > I don't think that is your only problem. You are not grouping by anything,
> > so your SUM() will fail.
>
> I deleted it when copying to usenet, of course I had:
>
> DELETE FROM customers WHERE customer_id IN (SELECT customer_id,
> SUM(accounting.amount) AS balance FROM customers JOIN accounting USING
> (customer_id) GROUP BY order_id HAVING balance > customers.limit)
>
> > Also, you should be doing this using the Multiple-table syntax with a join,
> > rather than a sub-query.
>
> Some years ago you suggested a join when I asked a similar question.
> But because I like subqueries for readability I thought I could fool
> you with the DELETE.

Have you tried moving the SUM(accounting.amount) to the HAVING clause?

But more to the point, whether you like sub-queries or not, they are
NOT the most efficient way to do this. Learn to do JOINs and you will
benefit in lots of ways.