Possible to Do a Delete with a Having Clause

Possible to do a delete with a HAVING clause?

Not really. The having clause implies an aggregation, which means you don't have the original rows any more.

I think you want the following:

DELETE from UserPredictions
where UserId in (select UserId from UserPredictions group by UserId having count(*) < 500)

DELETE FROM HAVING COUNT(*) in MySQL

As stated in the manual:

Currently, you cannot delete from a table and select from the same table in a subquery.

I think you'll have to perform this operation via a temporary table:

CREATE TEMPORARY TABLE temp
  SELECT   part_desc
  FROM     ag_master
  GROUP BY part_desc
  HAVING   COUNT(*) > 1000;

DELETE FROM ag_master WHERE part_desc IN (SELECT part_desc FROM temp);

DROP TEMPORARY TABLE temp;

Oracle SQL to delete table entries using having clause

Oracle supports delete join syntax, but it is a bit different than other databases.

DELETE FROM (
    SELECT a.*
    FROM MyTable a
    INNER JOIN
    (
        SELECT USER_ID
        FROM MyTable
        GROUP BY User_Id
        HAVING MAX(login_time) < (sysdate - 90)
    ) b ON a.USER_ID = b.USER_ID
);

Delete from statement using aggregates in HAVING clause SQL Server 2008

This should work:

;WITH cte As
(
    SELECT  *,
            MAX(eff_date) OVER(partition by drg) as MaxEffDate
    FROM    drgtable
)
DELETE 
FROM    cte
WHERE   eff_date <> MaxEffDate

Plus, plenty of new tricks in there for you to learn. :-)

(note: this does assume that you are on SQL Server 2005 or higher)

Turning a select statement with group and having clause into a delete?

try this :

delete from MyTable
where exists (
    select 1 from MyTable t2
    where MyTable.account_number=t2.account_number and MyTable.acc_cd=t2.acc_cd and MyTable.seq=t2.seq
)

Deleting a row from joined tables using group by and having clauses

The problem with your code is the subquery:

SELECT enrol.course_id group by enrol.course_id having count(*)<3

which, although is missing a FROM clause, it runs without a syntax error in MySql, but produces unexpected results.

Join the correct version of that subquery to the 2 tables like this:

DELETE c, e
FROM course c
LEFT JOIN enrol e ON e.course_id = c.id
LEFT JOIN (SELECT course_id, COUNT(*) count FROM enrol GROUP BY course_id) t
ON t.course_id = c.id
WHERE e.course_id IS NULL OR t.count < 3;

I use LEFT joins so that even courses with no enrolees will be deleted.

See a simplified demo.

SQL DELETE based on JOIN and aggregate condition

This answers the first question (about deleting from table 1)

DELETE t1
FROM Table1 AS t1
     INNER JOIN (
         SELECT
            cat,
            min_date=MIN([date])
         FROM
            Table2
         GROUP BY
            cat
    ) AS t2 ON
        t2.cat=t1.cat
WHERE
    t1.[date]>=t2.min_date

sql delete based on group by

delete p1
from point p1
join
(
  select user_id, trajectory_id 
  from point 
  group by user_id, trajectory_id
  having count(trajectory_id) < 5
) p2 on p1.user_id = p2.user_id
    and p1.trajectory_id = p2.trajectory_id 

---

Related Topics