Using a Having Clause in an Update Statement

Using a HAVING clause in an UPDATE statement

You can join to that subquery like so:

update n1 set
    isvalid = 0
from
    ncaastats n1
    inner join (
        SELECT
        FirstName, LastName, NCAAStats.AccountId, College_Translator.school_name, StatTypeId, COUNT(*) AS 'Count'
        FROM NCAAstats
        INNER JOIN College_Translator
        ON College_Translator.AccountID = NCAAstats.AccountId
        GROUP BY FirstName, LastName, NCAAStats.AccountId, College_Translator.school_name, CalendarYear, StatTypeId
        HAVING COUNT(*) >1
    ) n2 on
        n1.accountid = n2.accountid

Using HAVING in UPDATE with WHERE CLAUSE

From your comment to a previous answer I assume that you use MySql.

In MySql you need to join the table to a query that returns the duplicate images:

update tablename t inner join (
  select image   
  from tablename
  where id > 0
  group by image
  having count(*) > 1
) i on i.image = t.image
set vat = 2;

SQL UPDATE with having count

Perhaps?

update tablename t1
set A = (select max(A) from tablename t2 where t2.B = t1.B)
where B in (select B from tablename group by B having count(*) >= 2)

MySql how to use UPDATE with HAVING?

--updated intended targets
UPDATE table1 
SET    name_r = name_r + 1 
WHERE  id IN 
(
    -- return those ids again (to avoid the mysql #1093 error)
    SELECT  id
    FROM
    (   -- get all the ids for those names
        SELECT  id
        FROM    table1
        WHERE   name_co IN
        (   -- get all names that have more than one id
            SELECT   name_co
            FROM     table1 
            GROUP BY name_co 
            HAVING   COUNT(id) > 1
        )
    ) a
)

UPDATE MySQL table with HAVING clause

You can use a JOIN:

UPDATE mytable AS m
JOIN (SELECT slug
      FROM mytable 
      GROUP BY slug 
      HAVING COUNT(*) > 1) t
ON m.slug = t.slug      
SET m.slug = CONCAT(m.slug,'-',SUBSTRING(MD5(NOW()),1,2))

However the problem with the above is that it does not generate unique string values.

Demo here

One way to get around this, is to use row numbers calculated by variables:

UPDATE mytable AS m
CROSS JOIN (SELECT @row_number:=0, @slug := '') AS vars 
JOIN (
   SELECT slug
   FROM mytable 
   GROUP BY slug 
   HAVING COUNT(*) > 1 ) AS t ON m.slug = t.slug       
SET m.slug = CONCAT(m.slug, 
                    '-',
                    IF (@slug = m.slug,
                         IF (@slug := m.slug,@row_number:=@row_number+1, 
                                             @row_number:=@row_number+1),
                         IF (@slug := m.slug, @row_number:=1, 
                                              @row_number:=1)) 
                   )

Demo here

Access SQL UPDATE statement with HAVING clause

You can use a DSum() expression to avoid that "not updateable" complaint.

Also notice the second of these 2 conditions is not needed. When spQuoteStatus < 4, it can't be equal to 8, so adding the condition spQuotestatus <> 8 serves no purpose:

AND fq.spQuoteStatus < 4
AND fq.spQuotestatus <> 8

Assuming spQuoteStatus and fk_spQuoteID are both numeric datatype ...

UPDATE FactQuote AS fq 
SET fq.spQuoteStatus = 5
WHERE
        fq.quoteDate < #2016-5-10#
    AND fq.spQuoteStatus < 4 
    AND DSum("ItemTotal", "FactQuoteProduct", "fk_spQuoteID=" & spQuoteID) < 2000;

If those fields are text datatype, change the last condition to this ...

DSum("ItemTotal", "FactQuoteProduct", "fk_spQuoteID='" & spQuoteID & "'") < 2000

Create an Update statement using a Having Count clause on sqlplus

I don't the logic you are trying to build, but may be this can help

use an exists clause like

update headless outer
set hlse_term_start_date=null
where exists ( select 1 from headleas a, lernhist b
where a.hlse_ref=b.lerh_leas_hlse_ref
and a.HLSE_TERM_START_DATE is not null
and outer.hlse_ref = a.hlse_ref
group by a.hlse_ref, a.HLSE_STATUS_CODE, a.HLSE_TERM_START_DATE
having count(b.LERH_START_DATE)=1 
)

or

compare subquery count in where clause like

update headless outer
set hlse_term_start_date=null
where 1= (
select count(b.LERH_START_DATE)
from headleas a, lernhist b
where a.hlse_ref=b.lerh_leas_hlse_ref
and a.HLSE_TERM_START_DATE is not null
and a.hlse_ref = outer.hlse_ref
group by a.hlse_ref, a.HLSE_STATUS_CODE, a.HLSE_TERM_START_DATE
)

SQL UPDATE with CASE, GROUP BY, and HAVING

WITH CTE AS (
  SELECT stu.sc, stu.sn, COUNT(*) AS Total,
      CASE
        WHEN COUNT(*) = 3 Then 'Letter 1'
        WHEN COUNT(*) = 4 Then 'Letter 2'
        WHEN COUNT(*) = 5 Then 'Letter 3'
        ELSE 'SARB'
      END AS Letter  
  FROM STU  join att ON (stu.SC = att.SC and stu.SN = att.SN)
  WHERE att.al in ('c','t','u')
  GROUP by stu.sc, stu.sn
  HAVING COUNT(*) >= 3
)
UPDATE stu
SET stu.tru = cte.Letter
FROM stu  JOIN cte
   on (stu.sc = cte.sc and stu.sn = cte.sn)

---

Related Topics