1
I have a table where I need to apply a new watering, column A + B cannot have the same value.
I intend to add a rule like that:
ALTER TABLE minhatabela ADD CONSTRAINT minhaconstraintnome UNIQUE (coluna_A, coluna_B)
But to do this I need to remove the records that are "duplicated" of this value.
The requested rule is that any of the two or more records were deleted, I found that there are no more than two in each case so:
SELECT coluna_A, coluna_B, count(*)
FROM minhatabela
group by coluna_A, coluna_B
having count(*) > 1
So, I need to delete one of the two duplicate records, that’s over twenty thousand cases, so I wanted to do it in one command, but I don’t know how to do that.
Related: Mysql: How to remove duplicate lines efficiently?
– user25930
Perfect, it will work, as I mark it as an answer?
– Ricardo
I could tag as duplicate, but I’m in doubt because the other question is specifically about Mysql, and yours is about SQL Server. Although the answers there work perfectly in SQL Server...
– bfavaretto
Unless a DBMS has a proprietary mechanism to do this operation, I don’t think it makes sense to have N questions with the same answer (and even if a DBMS has, I think it makes more sense to detonate the tag
mysqlof that question and add the proprietary mechanism as an additional answer there).– user25930
@ctgPi A proprietary mechanism for this I do not know, but there are particularities of TSQL that can allow to do in other ways in SQL Server (with a USING, for example). But I’ll keep the community decision here. For now I won’t remove the mysql tag from there (but added sql), maybe this is worth a discussion in the meta. And thank you for your very relevant considerations.
– bfavaretto