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)