CODEWITHSUNDEEP
mysqlsql
lk121
lk121

Reputation: 1335

Sql query to delete rows for a specific condtion

i am trying to write query for the following

mysql db has two columns with data as follows

no id
1   1
1   2
1   3
2   4
3   5
3   6
4   7
5   8

i need to write query for the following condtion

when "no" repeats, delete all those rows from db where "no" is same and "id" > min of id for that "no"

expected output for the above table
no id
1   1
2   4 
3   5
4   7
5   8

Upvotes: 0

Views: 85

Answers (2)

Harsh Gupta
Harsh Gupta

Reputation: 4538

DELETE FROM tableName WHERE id NOT IN (
    SELECT MIN(id) FROM tableName GROUP BY no
)

HTH

Upvotes: 0

John Woo
John Woo

Reputation: 263913

DELETE  a
FROM    tableName a
        LEFT JOIN
        (
            SELECT  no, MIN(ID) min_ID
            FROM    tableName
            GROUP   BY no
        ) b ON a.no = b.no AND
                a.id = b.min_ID
WHERE   b.no IS NULL

The other way,

DELETE  a
FROM    tableName a
        INNER JOIN tableName b 
        ON a.no = b.no
WHERE   a.id > b.id

Upvotes: 3

Related Questions