How to remove duplicate tuples using SQL

Question

I have a table as follows

Col1  Col2
12    34
34    12

Given that these are duplicates, how do we delete them? I tried to solve this issue using self-training. But I can not get the required answer. Can anyone help?

Answer 1

you can use GREATEST and LEAST to “arrange” your columns, and then use different ones:

SELECT DISTINCT GREATEST(col1, col2) as first, LEAST(col1, col2) as second from yourTable

This will give you a great result. If you want to delete, you can delete everything that is not in this result:

DELETE FROM yourTable where (col1, col2) NOT IN (
  SELECT DISTINCT GREATEST(col1, col2) as first, LEAST(col1, col2) as second from yourTable
)

Answer 2

, : , A B, B A? , . , .

- ; , Col1, Col2. 13 -> 27 [13, 27]; 27 -> 13, [13, 27], .

, :

UPDATE IGNORE t
SET col1=(@temp:=col1), col1 = col2, col2 = @temp
WHERE col1 > col2;

( , , MySQL, , , ); :

DELETE FROM t
WHERE col1 > col2;

Answer 3

MySQL:

DELETE b 
FROM mytable a, mytable b 
WHERE a.col1 = b.col2 AND a.col2 = b.col1 AND a.col1 > b.col2;

, , , ?