Skip to content
Advertisement

Update column value based on the multiple rows

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)
User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement