update first record found

[huddles]

Hello,

I have the following query that finds all records which have count(col_A) > 1 and stat = '5'.

SELECT DISTINCT TOP 100 i1.egc_id,i1.stat
FROM dbo.table i1 INNER JOIN
dbo.table i2 ON i1.egc_ID = i2.egc_ID
GROUP BY i1.egc_ID, i2.egc_ID,i1.stat
HAVING (COUNT(i1.egc_ID) >= 2) and (i1.egc_id = i2.egc_id) AND i1.stat = 5)

Basic output is:

EGC_id Stat
1234546 1
1234546 1

or if the count is > 2 then

EGC_id Stat
1234546 1
1234546 1
1234546 1


what sql statement can be used to update the second record to '5' or for that matter the second and third? Using the query I have above or one that is even better is OK.

Help appreciated

 

[sguslan]

I am not sure i understood what columns you want to update, but the example below should help:

update dbo.table set SomeColumn=5
where egc in
(
select egc_id from
(
(SELECT DISTINCT TOP 100 i1.egc_id,i1.stat
FROM dbo.table i1 INNER JOIN
dbo.table i2 ON i1.egc_ID = i2.egc_ID
GROUP BY i1.egc_ID, i2.egc_ID,i1.stat
HAVING (COUNT(i1.egc_ID) >= 2)
and (i1.egc_id
=i2.egc_id) AND i1.stat = 5
)
AS T
)
as tt
)