Created
February 10, 2022 01:16
-
-
Save damiancipolat/2676708c0bbd9db738e438601836fee9 to your computer and use it in GitHub Desktop.
An sql example to remove duplicate fields in a table from by one column.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
drop table testtb; | |
drop table test_duplicated; | |
drop table test_unique; | |
create temporary table testtb ( | |
entity varchar(10) not null, | |
duplicate varchar(10) not null | |
); | |
insert into testtb | |
values | |
('a', 'b'), | |
('b', 'a'), | |
('a', 'c'), | |
('c', 'b'), | |
('c', 'x'), | |
('b', 'x'); | |
-- Traigo cantidad duplicados | |
select entity,count(*) | |
from testtb | |
group by entity | |
having count(*)>1; | |
-- Cargo todas las que tienen duplicados | |
SELECT a.* | |
into test_duplicated | |
FROM testtb a | |
JOIN ( | |
SELECT entity, COUNT(*) | |
FROM testtb | |
GROUP BY entity | |
HAVING count(*) > 1 | |
) b | |
ON a.entity = b.entity | |
ORDER BY a.entity | |
--Filtro por la 1ra | |
select distinct ttb.entity,ttb.duplicate | |
into test_uniques | |
from testtb as ttb | |
inner join test_duplicated as td on td.entity=ttb.entity | |
where ttb.duplicate=( | |
select a.duplicate | |
from test_duplicated as a | |
where a.entity=ttb.entity | |
limit 1 | |
) | |
--Aplico borrado | |
delete from testtb as tb | |
where not exists (select * from test_uniques as x where x.entity=tb.entity and x.duplicate=tb.duplicate) | |
select * from testtb |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment