query to update row

I have a table with structure :

Country |  DUPLICATE
India   |
Australia|
India   |
USA     |
Germany |
Germany |

I have to update DUPLICATEcolumn to 'Y' when the value in Country column is unique and to 'N' wh开发者_如何学JAVAen the value is not unique. I tried to accomplish this with the help of

select Country,dupe_count
count(*) over (partition by Country) as dupe_count
from newTable 

This query will return country name and a DUP column (conataing number of appearance of corresponding country field). But was not able to do it. Any idea how to do this or is there any better way to do this. Please help.

With the following test data ...

create table tq84_country (
  country varchar2(10) , 
  duplicate char(1)  check(duplicate in ('Y', 'N'))
);

insert into tq84_country (country) values ('India');
insert into tq84_country (country) values ('Australia');
insert into tq84_country (country) values ('India');
insert into tq84_country (country) values ('USA');
insert into tq84_country (country) values ('Germany');
insert into tq84_country (country) values ('Germany');

... this update statement should do:

update
  tq84_country a
set
  duplicate = (
    select 
      case when 
        count(*)  > 1 then 'Y' 
                      else 'N'
        end 
    from
      tq84_country b
    where
      a.country = b.country
);

Verification:

select * from tq84_country;

Not too sure with oracle - been a long time since i used it. But from memory it wasn't to dissimilar from mssql.

UPDATE newTable 
SET DUPLICATE = 'Y'
WHERE Country IN (
   SELECT COUNT(Country)
   FROM newTable
   GROUP BY Country
   HAVING Count(Country) > 1
)


UPDATE newTable 
SET DUPLICATE = 'N'
WHERE Country IN (
   SELECT COUNT(Country)
   FROM newTable
   GROUP BY Country
   HAVING Count(Country) = 1
)

in duplicate column you want to put 'N' when the value is not unique.. means value of column Country having duplicate record then you want to put N (No)

any way you can use following query easily to perform this task

update newTable  
set DUPLICATE =
case
when country in (select country from newTable group by country having count(*) > 1) then 
'N' -- if you got my previous point then you have to edit this line with 'Y'
else
'Y'
end;