Please help me with query syntax

[cjh999777]

I need some help with this query. I have a table that contains purchase records. This table we can call "Purchases". Within this table I have a column called "ptid" which is related to the client info and there can be multiple records with the same "ptid". There is also a "balance" column. What I need to do is create a query which sums the "balance" column and groups by "ptid". This will give me two colums one will be total balance and the other will be ptid. I then want to delete all of the records in the table that have a total balance of "0.00". I have not had a problem selecting the records and summing the balance column what i cant figure out is how to grab the ptid and then delete all of the records with a blance of 0.00. Can anyone help me out.

Thanks

 

[r937]

that's harder than it appears

what if you had two rows, one with a balance of 9.37 and the other with -9.37

when you group by, the "total balance" is 0.00

do you want to delete these too?

r937.com | rudy.ca

 

[cjh999777]

That is a good question and I think for the moment we will say that i want to delete all records from the table that have a sum of zero. If you can help shed some light on this it would be most helpfull.

Thanks

 

[r937]

could you show your query that finds these rows please

r937.com | rudy.ca

 

[cjh999777]

sure bleow is the query that finds the rows

select Sum(balance) as tltbal, ptid from ar__b23ccd as a group by ptid order by tltbal

Thanks

 

[r937]

i was hoping you'd have a HAVING clause because that's what you need here

try this --

select ptid from ar__b23ccd group by ptid having sum(balance) = 0.00

yes?


r937.com | rudy.ca

 

[cjh999777]

yes that sellects all of the records so now how would i delete them? Do i use a join?

 

[r937]

what happens when you do this --

delete
  from ar__b23ccd
 where ptid
       ( select ptid 
           from ar__b23ccd 
         group 
             by ptid 
         having sum(balance) = 0.00 )

r937.com | rudy.ca

 

[cjh999777]

I get the error below

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select ptid from ar__b23ccd group by ptid having sum(balance) = 0.00 )' at line 1

 

[cjh999777]

if i change your version by addin 'in' like this

delete
from ar__b23ccd
where ptid
in( select ptid
from ar__b23ccd
group
by ptid
having sum(balance) = 0.00 )

Then i get the following error:

You can't specify target table 'ar__b23ccd' for update in FROM clause

thanks

 

[r937]

yeah, sorry for the typo, forgot the IN keyword

well, it appears you cannot delete from the same table you're selecting from

have you ever created a temp table?

r937.com | rudy.ca

 

[cjh999777]

do u mean the following using the as clause?

select ptid from ar__b23ccd as temptable group by ptid having sum(balance) = 0

 

[cjh999777]

I have a query that works but it is really slow. Please let me know if there is a better way to do this so that it is quicker. Below is the query:

delete NOBAL.* from ar__b23ccd as NoBAL inner join (select ptid from ar__b23ccd group by ptid having sum(balance)=0) as temp on temp.ptid = NoBAL.ptid

thanks for all your help

 

[r937]

so you're actually deleting from a different table?

your first post certainly does not give that impression



r937.com | rudy.ca