Clever Geek Handbook

SQL - detect duplicate in a column only if they matter in another column

I have this MYSQL table:

Name    |  Age | X
--------------------
Andrew  |   7  |
Andrew  |   7  |
Andrew  |  10  |  
John    |   9  |
John    |  11  | 
John    |  11  |

And I would like to assign a value to those names that share age, for example:

Name    |  Age | X
--------------------
Andrew  |   7  | x
Andrew  |   7  | x
Andrew  |  10  |  
John    |   9  |
John    |  11  | x
John    |  11  | x

I don’t know how to do it.

+3


source to share


4 answers


If your database supports functions window

, then

Select Name,Age,
       case when count(Age)over(partition by Name, Age) > 1 then 'x' else '' end as X
FROM yourtable


If no ( Mysql

), then

Select Name,Age,
       case when (select count(Age) from yourtable b where a.Name = b.Name and a.Age = b.Age) > 1 then 'x' else '' end as X
FROM yourtable a
+2


source


Since MySQL does not support window functions, you must read the table twice. Here's one way to do it:



select name, age, dups.x
from persons p
left join
(
  select name, age, 'x' as x
  from persons
  group by name, age
  having count(*) > 1
) dups using (name, age)
order by name, age;
+1


source


- duplicate column detection with all columns

    with cte as
    (select name, age ,x , row_number () over (partition by name,age,x order by 
   name) RowNum from Table 
    ) select name,age,x from cte where rownum > 1
0


source


Of course this one will work. Just try it ... :). Easy and smart!

   Select Name,Age,count(*)   
   INTO #TempTable 
   FROM yourtable 
   group by   Name,Age
   having count(*) >1


update a 
set a.X= "X"
from yourtable a 
      join #TempTable b on  a.Name=b.Name and a.Age =b.Age

drop table #TempTable
0


source






More articles:


All Articles