-1

I had this table

A B
1 1H
2 1H
1 1G
1 1E

I need update on this table column A, that take max(a) group by B having count(*) = 2.

Result will be like this:

A B
2 1H
2 1H
1 1G
1 1E
jarlh
  • 42,561
  • 8
  • 45
  • 63
Jan Papota
  • 49
  • 5
  • Don't store data that is calculated from other table data - you will end up with inconsistent data. Create a view instead. – jarlh Oct 24 '18 at 09:55
  • This is one time script used after first data load to the table. I need set same A value to grouped B, but only if B having count = 2 – Jan Papota Oct 24 '18 at 09:59
  • Could you try https://stackoverflow.com/questions/8793914/using-a-having-clause-in-an-update-statement this solutions – Singh Kailash Oct 24 '18 at 10:03
  • Add some more sample data, and its new result as well. (To make the problem clearer.) – jarlh Oct 24 '18 at 10:07

2 Answers2

0

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)
jarlh
  • 42,561
  • 8
  • 45
  • 63
0

You can try below

update tablename t1
set A = (select count(*) from tablename t2 where t2.B = t1.B having count(*)=2)
Fahmi
  • 37,315
  • 5
  • 22
  • 31