Using HAVING in UPDATE with WHERE CLAUSE

Question

i am trying to update a table by setting column VAT TO 2 where we have duplicate values in column image

SET 
    VAT = 2
WHERE id >0  
 HAVING count(image) > 1

i simply want to update VAT where there are duplicate values in image column — meetmec12, Sep 07 '19 at 09:58
Which [DBMS](https://en.wikipedia.org/wiki/DBMS) product are you using? "SQL" is just a query language, not the name of a specific database product. Please [add a tag](https://meta.stackoverflow.com/questions/388759/why-should-i-tag-my-rdbms) for the database product you are using `postgresql`, `oracle`, `sql-server`, `db2`, ... — a_horse_with_no_name, Sep 07 '19 at 10:00

Sergey Kalinichenko · Answer 1 · 2019-09-07T10:13:02.997

0

You could do this:

UPDATE applicantinfo
SET VAT = 2
WHERE image IN (
    SELECT image
    FROM (SELECT * FROM applicantinfo)
    WHERE id > 0
    GROUP BY image
    HAVING COUNT(*) > 1
)

SELECT inside the WHERE clause supplies duplicate images of rows with ids above zero.

edited Sep 07 '19 at 10:13

answered Sep 07 '19 at 09:58

Sergey Kalinichenko

697,062
78
1,055
1,465

Table 'applicantinfo' is specified twice, both as a target for 'UPDATE' and as a separate source for data – meetmec12 Sep 07 '19 at 10:08
1

applicantinfo is MyTable in your query – meetmec12 Sep 07 '19 at 10:09
1

Since this is MySql, [you need to use a little trick](https://stackoverflow.com/q/44970574/335858). See my edit. – Sergey Kalinichenko Sep 07 '19 at 10:14

DarkRob · Answer 2 · 2019-09-07T10:10:19.257

0

You may use this.

For SQL Server

update t set t.VAT = 2 from applicantinfo as t inner join
(select Image from applicantinfo group by image having count(*)>1) as b 
on t.image = b.image

edited Sep 07 '19 at 10:10

answered Sep 07 '19 at 10:09

DarkRob

3,737
1
8
27

3

That's non-standard SQL. Please mention for which DBMS product this works – a_horse_with_no_name Sep 07 '19 at 10:09

score 0 · Accepted Answer · answered Sep 07 '19 at 10:11

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;

Using HAVING in UPDATE with WHERE CLAUSE

3 Answers3