sql server - Aggregate function with where clause -

sql server - Aggregate function with where clause -

i have situation need aggregate function utilize in status update statement , select statement.

example:

situation 1: update test_table set id_number = 0 count(id_number)=1; /* not possible */

note: above script not possible clause.

situation 2:

for illustration have next table: test

id_number column1 column2 -------------------------- 1 xyz zyx 1 mmm nnn 2 iii jjj 3 aaa bbb 3 ccc ddd

now need show records who's id_number appear 2 times.

expected result:

id_number column1 column2 ------------------------------ 1 xyz zyx 1 mmm nnn 3 aaa bbb 3 ccc ddd

my try:

select * test grouping id_number,column1,column2 having count(id_number)>1;

note: gives me nil in result.

set nocount on; declare @test table ( id_number int not null, column1 varchar(50), column2 varchar(50) ); insert @test values (1, 'xyz', 'zyx'); insert @test values (1, 'mmm', 'nnn'); insert @test values (2, 'iii', 'jjj'); insert @test values (3, 'aaa', 'bbb'); insert @test values (3, 'ccc', 'ddd'); -- situation #1 ;with cte ( select id_number @test grouping id_number having count(*) = 1 ) update ts set ts.id_number = 0 @test ts inner bring together cte on cte.id_number = ts.id_number; -- situation #2 ;with cte ( select id_number @test grouping id_number having count(*) > 1 ) select ts.* @test ts inner bring together cte on cte.id_number = ts.id_number;

the output "expected result" shown in question.

for both situations, cte gets id_number values show more once, , list of values used filter main select or update.

sql-server sql-server-2008-r2