updating a record with conditions

[tirf]

I have this table

create table tbl1 (id int, flag1 varchar(100), flag2 varchar(100))

insert into tbl1
select 1, 'Yes', 'No' union all
select 1, 'Yes', 'No' union all
select 1, 'Yes', 'Yes' union all
select 2, 'Yes', 'No' union all
select 2, 'Yes', 'No' union all
select 2, 'Yes', 'No' union all
select 2, 'Yes', 'Yes' union all
select 3, 'Yes', 'No' union all
select 3, 'Yes', 'No' union all
select 4, 'Yes', 'No' union all
select 4, 'Yes', 'Yes' union all
select 5, 'Yes', 'No' union all
select 5, 'Yes', 'Yes' union all
select 5, 'Yes', 'Yes' union all
select 5, 'Yes', 'Yes'

one ID must have 2 'Yes' values for flag1; if there are more than 2 we should update the extra Yes's to No based on these conditions

update the extra yes's in flag1 preferebly with 'yes' value for flag2

this should be the output

ID     flag1      flag2

1	Yes	   No
1	Yes	   No
1	No	   Yes
2	Yes	   No
2	Yes	   No
2	No	   No
2	No	   Yes
3	Yes        No
3	Yes	   No
4	Yes        No
4	Yes	   Yes
5	Yes	   No
5	Yes	   Yes
5	No	   Yes
5	No	   Yes

Thanks,

 

[RyanEK]

If you had an extra field on the table you could use it to store the row_number() within each group and do an update this way.

There would be another solution if you had a field that determined the sorting, but you don't so I think you'll need to use a cursor for this one.

 

[Crowley16]

you would definitely need some sort of ordering to do this...

If you're using 2005, then use the row_number() function mentioned by Ryan. However you can achieve this within a cte, and then use the cte as the basis for the update, something like:

;with a as
(
   select *
      , row_number() over (partition by id order by flag1) as row
      , row_number() over (order by id, flag1) as pk
   from tbl1
)
update tbl1 
set flag1 = 'no '
where pk in 
   (
      select pk
      from tbl1
      where id in 
         (
            select id
            from tbl1
            where flag1 = 'yes'
            group by id
            having count(flag1) > 2
         )
         and row > 2
   )

Basically, what this is doing is first numbering the records by each id, then generating a primary key over all the rows.
Then using these numbers, it looks at all id's with more than 2 yes, and returns just the pk's which satisfy this.
Finally it updates the found rows to be 'NO'.

The code is just something I knocked together off the top of my head, and will undoutably be wrong, so don't copy and paste... but it should give you an idea of how this could be done.

There's probably quicker ways out there as well...

--------------------
Procrastinate Now!

 

[tirf]

Thank you all for your suggestion; row_number worked for me

Thanks again