I have a table example: (Col3 is bit datatype)
col1 | col2 | col3 ____________________ abc | xyz | 0 abc | xyz | 1 abc | xyz | 0 abc | xyz | 0
I want to select distinct from the table except for col3 and update col3 value to 0 if col3 values are different. (If all col3 values are same then distinct will return distinct rows).
Here I am expecting the output to be:
col1 | col2 | col3 ____________________ abc | xyz | 0
Any help?
Edit
When col3 values are same:
col1 | col2 | col3 ____________________ abc | xyz | 0 abc | xyz | 0 abc | xyz | 0 abc | xyz | 0 col1 | col2 | col3 ____________________ abc | xyz | 1 abc | xyz | 1 abc | xyz | 1 abc | xyz | 1
EDIT
Further if I want to update only few of the rows then how can that be done? Example:
If there exists data :
col1 | col2 | col3 ____________________ abc | xyz | 0 abc | xyz | 1 abc | xyz | 1 abc | xyz | 0 qwe | asd | 1 qwe | asd | 0 qwe | asd | 0 qwe | asd | 0
Expected output:
col1 | col2 | col3 ____________________ abc | xyz | 0 qwe | asd | 0
Advertisement
Answer
You can try the following query using row_number()
function, cte
and inner join
as shown below.
Note: If you want to update values with a duplicate one and keep one from those entries as it is in that case you do not need to join with the table again.
create table SampleTable( col1 varchar(10) , col2 varchar(10) , col3 bit) insert into SampleTable values ('abc', 'xyz', 0), ('abc', 'xyz', 1), ('abc', 'xyz', 0), ('abc', 'xyz', 0) --Before update select * from SampleTable --Getting rows with duplicate ; with cte as (SELECT col1 , col2 , col3 , row_number() over (partition by col1, col2, col3 order by (select null)) as cnt FROM SampleTable )UPDATE t set t.col1 = t.col1 , t.col2 = t.col2 , t.col3 = 1 from SampleTable t inner join cte on t.col1 = cte.col1 and t.col2 = cte.col2 and t.col3 = cte.col3 and cnt > 1 --After update select * from SampleTable
Here is the live db<>fiddle demo.
Here is an another way using exists
.
Second approach
update t1 set t1.col1 = t1.col1 , t1.col2 = t1.col2 , t1.col3 = 1 from SampleTable t1 where exists (select 1 from SampleTable t2 where t1.col1 = t2.col1 and t1.col2 = t2.col2 and t1.col3 = t2.col3 group by col1, col2, col3 having count(*) > 1)