delete having more than 1 count

[foxup]

Hi all,

I'm having an issue with a "DELETE FROM... HAVING"... How can I make this command work please?

delete * FROM bell_lns where newtob = .T. and HAVING COUNT('custno')>1 and custno in (sele custno from test1)


Thanks,
FOXUP

 

[jedraw]

Try
DELETE FROM bell_lns
WHERE newtob = 1 AND custno IN
(
SELECT custno FROM test1
GROUP BY custno
HAVING COUNT(custno) > 1
);

 

[Joe Crescenzi]

Try
DELETE FROM bell_lns
WHERE newtob = 1 AND custno IN
(
SELECT custno FROM test1
GROUP BY custno
HAVING COUNT(custno) > 1
);

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.

 

[foxup]

nope, syntax error and I need it where the table bell_lns HAVING COUNT('custno')>1

 

[Joe Crescenzi]

Hi all,

I'm having an issue with a "DELETE FROM... HAVING"... How can I make this command work please?

delete * FROM bell_lns where newtob = .T. and HAVING COUNT('custno')>1 and custno in (sele custno from test1)


Thanks,
FOXUP

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]

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.

Tip​
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.

 

[foxup]

I did it it 2 commands like Paul said and it works. Thank you.

 

[jedraw]

Is this what worked ?
DELETE FROM MyTable WHERE CustNo IN (
SELECT CustNo FROM MyTable WHERE CustNo IN (SELECT CustNo FROM Test1)
GROUP BY CustNo HAVING COUNT(*) > 1 );

 

[igorsandler]

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.

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]

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

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.