CODEWITHSUNDEEP
sqlsql-serverdistinct
Arti
Arti

Reputation: 3071

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

Upvotes: 0

Views: 116

Answers (5)

Norby
Norby

Reputation: 57

For your specific example you can group by(col1,col2) and then take min(col3)

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can use EXISTS : 

UPDATE t
       SET t.col3 = (CASE WHEN EXISTS (SELECT 1 
                                       FROM table t1 
                                       WHERE t1.col1 = t.col1 AND 
                                             t1.col2 = t.col2 AND
                                             t1.col3 <> t.col3
                                      ) 
                          THEN 0 ELSE 1
                     END) 
FROM table t;

Upvotes: 1

Suraj Kumar
Suraj Kumar

Reputation: 5643

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)

Upvotes: 1

forpas
forpas

Reputation: 164099

You can group by col1, col2 and a case expression for col3:

select col1, col2,
  case when min(cast(col3 as int)) = max(cast(col3 as int)) then 1 else 0 end col3
from tablename
group by col1, col2

See the demo.
Results:

> col1 | col2 | col3
> :--- | :--- | ---:
> abc  | xyz  |    0

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Is this what you want?

select distinct col1, col2, 0 as col3
from t;

Or perhaps:

select col1, col2, 0 as col3
from t
group by col1, col2
having min(col3) <> max(col3)
union all
select col1, col2, col3
from t
where not exists (select 1
                  from t
                  where t2.col1 = t.col1 and t2.col2 = t.col2 and
                        t2.col3 <> t.col3
                 );

This can also be expressed with window functions:

select col1, col2,
       (case when min_col3 = max_col3 then max(col3) else 0 end) as col3
from (select t.*,
             row_number() over (partition by col1, col2 order by col3) as seqnum,
             max(col3) over (partition by col1, col2) as max_col3,
             min(col3) over (partition by col1, col2) as min_col3
     from t
    ) t
group by col1, col2, min_col3, max_col3,
         (case when min_col3 = max_col3 then seqnum else 0 end)

Upvotes: 1

Related Questions