Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
delete having more than 1 count
- Thread starter foxup
- Start date
Solution
Joe Crescenzi
Programmer
BE CAREFUL of lines like that in FoxPro, since you didn't include a semicolon, it will interpret the first line before throwing an error when it reaches the second line.
So that first line deletes EVERY row in the table before it reaches the (incomplete) second line.
- Thread starter
- #4
Joe Crescenzi
Programmer
The HAVING parameter is intended for aggregate functions, so you can't use it like that.
Since you seem to be new to SQL Queries, you should avoid using the delete function until you can first successfully do a SELECT with the intended results.
For example:
SELECT CustNo, count(*) as MyCount from MyTable where Custno in (select CustNo from Test1) group by CustNo having MyCount > 1
If you see what you intended to see in that result, you can swap the word SELECT with DELETE with the same results.
Again, be CAREFUL with DELETE using SQL because you need to know what it would've selected before you trust your code to do a delete.
Paul Gibson
Programmer
You've just said "having an issue", but haven't described what the issue is. More details will provide more information to those attempting to help.
As the other people who've responded have suggested you are using the HAVING clause but haven't used GROUP BY, this isn't completely invalid but if you aren't using GROUP BY then HAVING will act like WHERE and the help suggests using WHERE instead for faster performance in some circumstances.
From the help...
SELECT - SQL Command - HAVING Clause
The HAVING clause specifies conditions that determines the groups included in the query. If the SQL SELECT statement does not contain aggregate functions, you can use a SQL SELECT statement that contains a HAVING clause without a GROUP BY clause.
One of things you could do is FIRST write a specific SQL - SELECT that pulls out only the records that you want to delete, then when you are happy that the SELECT is working 100% you can try altering it slightly to convert it from a SELECT to a DELETE, see if that works for you.
...Or, you can keep the SELECT working as a select, where it just pulls out the PKs of the records that you want to delete and then write a DELETE that uses the simple list of PKs in a much simpler command than trying to DELETE ... FROM ... HAVING all in one go. As others have said, you need to have total confidence in any DELETE - SQL command as it'll batch mark records for deletion.
It is interesting that the DELETE - SQL command topic in the help doesn't mention using HAVING anywhere. I'm not going to say it's impossible but I can say I've never done it myself so you might need to do your delete in two steps, as suggested above: 1. SELECT - SQL to identify what records you want deleting, then 2. DELETE using the results of step 1.
As the other people who've responded have suggested you are using the HAVING clause but haven't used GROUP BY, this isn't completely invalid but if you aren't using GROUP BY then HAVING will act like WHERE and the help suggests using WHERE instead for faster performance in some circumstances.
From the help...
SELECT - SQL Command - HAVING Clause
The HAVING clause specifies conditions that determines the groups included in the query. If the SQL SELECT statement does not contain aggregate functions, you can use a SQL SELECT statement that contains a HAVING clause without a GROUP BY clause.
|
|---|
| The HAVING clause without a GROUP BY clause acts like the WHERE clause. If the HAVING clause contains no aggregate functions, use the WHERE clause for faster performance. |
One of things you could do is FIRST write a specific SQL - SELECT that pulls out only the records that you want to delete, then when you are happy that the SELECT is working 100% you can try altering it slightly to convert it from a SELECT to a DELETE, see if that works for you.
...Or, you can keep the SELECT working as a select, where it just pulls out the PKs of the records that you want to delete and then write a DELETE that uses the simple list of PKs in a much simpler command than trying to DELETE ... FROM ... HAVING all in one go. As others have said, you need to have total confidence in any DELETE - SQL command as it'll batch mark records for deletion.
It is interesting that the DELETE - SQL command topic in the help doesn't mention using HAVING anywhere. I'm not going to say it's impossible but I can say I've never done it myself so you might need to do your delete in two steps, as suggested above: 1. SELECT - SQL to identify what records you want deleting, then 2. DELETE using the results of step 1.
- Thread starter
- #7
igorsandler
Programmer
Completely agree. I would go even further and I would create an updatable view, browse it first and if I like what I see, DELETE ALL==> TABLEUPDATE()===>Requery(), unless FoxUp is not familiar with views
Joe Crescenzi
Programmer
As a developer if I could make one change to SQL is that they remove the ability to use the DELETE command without any conditions.
The example code from @jedraw is a perfect example of how dangerous it can be. If you accidentally leave out the semicolon that lets FoxPro know there are additional lines, it would delete every row in the table.
For what it's worth, "delete from tablename" with no additional parameters is the equivalent of ZAP, which is worthy of an "Are you sure?" prompt when SAFETY is turned OFF. I'd even go a step further and I'd love to see a SQL Server configuration option to block DELETE FROM queries that have no additional WHERE clauses.
Developers who actually want to delete all the rows would simply add a WHERE clause that is always true, such as WHERE 1=1.
Chriss Miller
Programmer
- Aug 14, 2024
- 104
Foxup, could you post how you now did it?
You might think you got what you wanted, but may have deleted too many records, you might have got what you want and could help future forum visitors with that solution.
What I'd add is that I don't think DELETE even supports GROUP BY or HAVING at all. You can do correlated DELETEs by joining further tables and deleting on conditions that are based on joind data, but I don't see VFP DELETE-SQL support GROUP BY at all and then also not HAVING.
The basic recipe of selecting the records you want to delete and finally delete them by using a distinct list of primary keys from the select query is the normal thing to do.
And all in all it looks like you want to remove duplicates, well that's easier done with an index and non SQL traversing of a table.
And then some advice on details:
1. Having indeed needs no group by (a) , you can also count without a group by (b), but count in a having clause needs a group by.
a) SELECT * FROM yourtable HAVING newtob = .T. && having acts as where, as others already said
b) SELECT COUNT(*) FROM yourtable && simply counts all records, all data is seen as one group
but what won't work is
SELECT something FROM yourtable HAVING count(*)>1
You do want counts per custno, that's not done by Count(custno) as in "count per custumer number", no that's done by group by custno:
SELECT Count(*), custno FROM yourtable GROUP BY custno && gives all counts in detail
or
SELECT custno FROM yourtable GROUP BY custno HAVING Count(*)>1 && only custno with counts of 2 or higher without seeing the count.
As your goal is to delete you don't need to see the count, you just ensure it is a custno that is duplicate. But then, if you could do that with DELETE, too, you'd delete both records, not just the duplicate.
Count(custno) vs Count(*) only differs, if custno could be null. I don't think that's the case, but it could only make a difference in a case I would need to take a bit more time to construct, it's rarely what you need.
You might think you got what you wanted, but may have deleted too many records, you might have got what you want and could help future forum visitors with that solution.
What I'd add is that I don't think DELETE even supports GROUP BY or HAVING at all. You can do correlated DELETEs by joining further tables and deleting on conditions that are based on joind data, but I don't see VFP DELETE-SQL support GROUP BY at all and then also not HAVING.
The basic recipe of selecting the records you want to delete and finally delete them by using a distinct list of primary keys from the select query is the normal thing to do.
And all in all it looks like you want to remove duplicates, well that's easier done with an index and non SQL traversing of a table.
And then some advice on details:
1. Having indeed needs no group by (a) , you can also count without a group by (b), but count in a having clause needs a group by.
a) SELECT * FROM yourtable HAVING newtob = .T. && having acts as where, as others already said
b) SELECT COUNT(*) FROM yourtable && simply counts all records, all data is seen as one group
but what won't work is
SELECT something FROM yourtable HAVING count(*)>1
You do want counts per custno, that's not done by Count(custno) as in "count per custumer number", no that's done by group by custno:
SELECT Count(*), custno FROM yourtable GROUP BY custno && gives all counts in detail
or
SELECT custno FROM yourtable GROUP BY custno HAVING Count(*)>1 && only custno with counts of 2 or higher without seeing the count.
As your goal is to delete you don't need to see the count, you just ensure it is a custno that is duplicate. But then, if you could do that with DELETE, too, you'd delete both records, not just the duplicate.
Count(custno) vs Count(*) only differs, if custno could be null. I don't think that's the case, but it could only make a difference in a case I would need to take a bit more time to construct, it's rarely what you need.
Last edited:
Not a foxpro person, I was looking at the SQL. Unfamiliar with this syntax requirement.
I see this is what you mean:
DELETE FROM MyTable ;
WHERE CustNo IN ;
(SELECT CustNo FROM MyTable ;
WHERE CustNo IN ;
(SELECT CustNo FROM Test1) ;
GROUP BY CustNo ;
HAVING COUNT(*) > 1)
I see this is what you mean:
DELETE FROM MyTable ;
WHERE CustNo IN ;
(SELECT CustNo FROM MyTable ;
WHERE CustNo IN ;
(SELECT CustNo FROM Test1) ;
GROUP BY CustNo ;
HAVING COUNT(*) > 1)
Chriss Miller
Programmer
- Aug 14, 2024
- 104
I think foxup is looking for duplicate custno in "MyTable", not in "Test1". But I could be wrong, if Foxup won't post what finally solved it, I'd say test1 is just for filtering to only process some custno, not all of them. Otherwise, yes, that's generally the right SQL construct to make a SELECT subquery that does the group by custno and counting how many of the same. If my assumption is correct and test1 is a filter you'd only have single custno in it and you could also do an inner join instead of the IN (subquery). And I'd prefer the inner join to the IN (subquery) construct, though both work, in VFP query optimization by index (rushmore optimization) would be straight forward with a join condition, though an IN (subquery) would also be an index seek in the custno of test1. But if that's just generated for filtering it may not have the index, the main table would have, though. That's why an inner join could be optimized and IN not so.
Last edited:
Similar threads
