1

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

MisterPi
  • 1,471
  • 5
  • 17
  • 23

6 Answers6

2
delete t from t inner join t t2 on t2.number = t.number and t2.id < t.id
David דודו Markovitz
  • 42,900
  • 6
  • 64
  • 88
1

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.

Charles Bretana
  • 143,358
  • 22
  • 150
  • 216
  • Error Code xxxx: Comment contains no useful information – Charles Bretana Dec 02 '16 at 15:37
  • This is an opportunity to improve your comment and make it useful. Would you like a lesson in communication? – Charles Bretana Dec 02 '16 at 15:57
  • After adding the missing `from` we're getting a new error code - `Error Code: 1093. You can't specify target table 't' for update in FROM clause`. I hope this is clear enough. – David דודו Markovitz Dec 02 '16 at 16:03
  • Actually, no, it is not. this must be a syntactical requirement of MySQL (which I do not have), and the error message is certainly not informative either, Neither the main SQL statement query, nor the subquery in my SQL is an update query. The other SQL is a Delete, and the subquery is a Select. Both reference table `t`. Which reference is the error message referring to? – Charles Bretana Dec 02 '16 at 16:48
  • I interperate this as - The deleted ("updated") table can't be referenced in the `from` of the sub-query. – David דודו Markovitz Dec 02 '16 at 16:53
  • then `t` is obviously not the name of the table. see edited answer. the table needs to be aliased so that the subquery's Where clause predicate can reference both the `number` attribute in the subquery reference to the table and the `number` attribute of the outer query table. – Charles Bretana Dec 02 '16 at 16:56
  • in a subquery, references toattributes (columns) are assumed to be in tables in that subquery (if they exist) and the query processor will only look in the outer query if it cannot find a column with that name on one of the tables referenced in the subquery. So in my answer, `...where number = t.number ...` is, effectively, the same as `...where myTable.number = t.number ...` – Charles Bretana Dec 02 '16 at 17:00
1

You can do something like

DELETE FROM your_table
WHERE (number2 = 2)
    AND (number1 <> 1);
Sebastian M
  • 471
  • 1
  • 4
  • 20
0

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

kapilpatwa93
  • 4,111
  • 2
  • 13
  • 22
0

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

farbiondriven
  • 2,450
  • 2
  • 15
  • 31
0

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.

Akshay Bhardwaj
  • 65
  • 2
  • 12