CODEWITHSUNDEEP
sqloracle11g
facebook
facebook

Reputation: 1864

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' when 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.

Upvotes: 0

Views: 105

Answers (3)

pratik garg
pratik garg

Reputation: 3342

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;

Upvotes: 0

René Nyffenegger
René Nyffenegger

Reputation: 40499

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;

Upvotes: 1

TheITGuy
TheITGuy

Reputation: 722

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
)

Upvotes: 0

Related Questions