CODEWITHSUNDEEP
mysqlsql
MisterPi
MisterPi

Reputation: 1671

How to delete data from SQL except one row?

I have some rows in table with the same number.

How to delete all rows except one row?

I mean this:

 number
1 2
2 2
3 2
4 2

So, I need to delete all rows where number = 2, but leave only one row, for example: 1 2

Upvotes: 1

Views: 187

Answers (6)

Charles Bretana
Charles Bretana

Reputation: 146603

First, what is the real name of the table? It's not really named t, is it? Let's assume it's named MyTable. Second, Is the first column the primary Key? If so, then 

Delete from MyTable t 
where PK !=
    (Select Min(PK) from MyTable
     where number = t.number)

NOTE: (this should be obvious) Please correct for whatever MySQL syntactical requirements demand for Deletes with subqueries.

Upvotes: 1

Sebastian M
Sebastian M

Reputation: 491

You can do something like

DELETE FROM your_table
WHERE (number2 = 2)
    AND (number1 <> 1);

Upvotes: 1

Akshay Bhardwaj
Akshay Bhardwaj

Reputation: 65

Suppose we Have 2 columns in table tb1:

ID   Name
1    Akshay
2    Akshay 
3    Akshay

In this I want to delete every row except the one with ID 2,

So I'll write the SQL statement as:

delete from tb1 where ID NOT LIKE 2

this will delete evey row except the second row.

Hope this helps.

Upvotes: 0

farbiondriven
farbiondriven

Reputation: 2468

delete from t where number=.. and pk > (select min(pk) from t)

Upvotes: 0

David דודו Markovitz
David דודו Markovitz

Reputation: 44991

delete t from t inner join t t2 on t2.number = t.number and t2.id < t.id

Upvotes: 2

kapilpatwa93
kapilpatwa93

Reputation: 4411

This will delete every row except 1st row

delete from table_name t1 
where pk_col not in (select pk_col from table_name where number = t1.number limit 1)

Assumption pk_col = primary key col

Upvotes: 0

Related Questions