Update rows in table

I have a table (Fruits) with following column

Fruit_Name(varchar2(10)) | IsDuplicate Number(1)
            Mango                    0
           Orange                    0
           Mango                     0

What i have to do is to update IsDuplicate column to 1 where Fruit_Name in Distinct i.e

 Fruit_Name(varchar2(10)) | IsDuplicate Number(1)
            Mango                    1
           Orange                    1
           Mango                   开发者_JS百科  0

How should I do this?

This should do it as far as I can tell

update fruits 
  set is_duplicate = 
      (
        select case 
                  when dupe_count > 1 and row_num = 1 then 1
                  else 0
                end as is_dupe
        from (
           select f2.fruit_name,
                  count(*) over (partition by f2.fruit_name) as dupe_count,
                  row_number() over (partition by f2.fruit_name order by f2.fruit_name) as row_num,
                  rowid as row_id
           from fruits f2
        ) ft 
        where ft.row_id = fruits.rowid
          and ft.fruit_name = fruits.fruit_name
      )

Edit
But instead of actually updating the table, why don't you create a view that returns the information. Depending on the size of the table it might be more efficient.

create view fruit_dupe_view
as 
select fruit_name,
       case 
          when dupe_count > 1 and row_num = 1 then 1
          else 0
        end as is_duplicate
from (
   select fruit_name,
          count(*) over (partition by fruit_name) as dupe_count,
          row_number() over (partition by fruit_name order by fruit_name) as row_num
   from fruits 
) ft 

Straight and simple -- you can't. Not with vanilla SQL. SQL is a set-based processing language, and you do things in sets. There is no way for SQL to know which one of your many Mango's should be tagged 1. You can probably tag one of them with 1 using windowing functions or ROWNUM etc. in a SELECT, but I don't think it can be done with an UPDATE.

In other words, your table lacks a unique key in the first place, so it is not something that SQL is designed to process.

However, you may try adding a sequential primary key to each row. Then you can easily write an UPDATE query to set to 1 all the rows with COUNT > 1 and key = MIN(key).

In other words, you really have to look at your database design. Relational databases are not supposed to contain "duplicates". That fact that you need to mark something as a duplicate means that your tables are designed wrong in the first place. The database should not even allow duplications to enter into its data.