Skip to content
Advertisement

Update column value based on the multiple rows

I have a table example: (Col3 is bit datatype)

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:

Any help?

Edit

When col3 values are same:

EDIT

Further if I want to update only few of the rows then how can that be done? Example:

If there exists data :

Expected output:

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.

Here is the live db<>fiddle demo.

Here is an another way using exists.

Second approach

User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement