I'm trying to solve a question via SQL ...
Let's suppose this is my table:
NAME | ITEM1 | ITEM2 | ITEM3
AAA 1 2 1
BBB 2 1 3
CCC 3 2 1
DDD 3 1 2
EEE 1 3 1
Now, 1 and 2 are the values I have to keep stored in. The 3 value is the one I have to modify in every column ...now, this value must be changed to 1 until reaching a defined total, else it must be changed to 2.
For example : in the ITEM1 column, let's suppose I need to have three times the value 1. It means I should modify one of the two 3 present values with 1 (It doesn't matter which) and the other one with 2. And so on for all the remaining columns ...
Could you please help me finding a quick way to do this?
I dont know if this will work for your case, but it does what you want. The problem is size of the query (2 updates for each column). obs. #teste is your table
declare @max_number_1_per_column int = 3
declare @coun_1 int
--assuming that you need to update all columns with the same rule (max number 1 in each column is 3, in this example)
--assuming name is unique
select @coun_1 = count(*) from #teste where item1 = 1
if (@max_number_1_per_column - @coun_1 > 0)
update #teste
set item1 = 1
where name in (select top(@max_number_1_per_column - @coun_1) name from #teste where item1 = 3)
update #teste
set item1 = 2
where item1 = 3
---other column
select @coun_1 = count(*) from #teste where item2 = 1
if (@max_number_1_per_column - @coun_1 > 0)
update #teste
set item2 = 1
where name in (select top(@max_number_1_per_column - @coun_1) name from #teste where item2 = 3)
update #teste
set item2 = 2
where item2 = 3
---other column
select @coun_1 = count(*) from #teste where item3 = 1
if (@max_number_1_per_column - @coun_1 > 0)
update #teste
set item3 = 1
where name in (select top(@max_number_1_per_column - @coun_1) name from #teste where item3 = 3)
update #teste
set item3 = 2
where item3 = 3