0

I have been searching to find a proper solution. Following is my table.i don't want to use a temporary table. Please help me

mysql> select * from Employees;

+-------+------+-----+--------+
| Empid | Name | Did | Salary |
+-------+------+-----+--------+
| 123   | a    | 1   |  10000 |
| 321   | b    | 2   |  20000 |
| 421   | c    | 2   |  20000 |
| 521   | c    | 2   |  20000 |
| 621   | e    | 2   |  35000 |
| 821   | b    | 1   |  30000 |
| 4545  | s    | 1   |     14 |
| 1111  | k    | 1   |      0 |
| 1111  | k    | 1   |      0 |
| 1111  | k    | 1   |   3445 |
| 1111  | k    | 1   |      0 |
| 1111  | k    | 1   |  35000 |
| 1111  | k    | 6   |  35000 |
| 123   | a    | 1   |  10000 |
+-------+------+-----+--------+
14 rows in set (0.01 sec)

I tried using @rownum but throwing the following error

ERROR 1093 (HY000): You can't specify target table 'Employees' for update in FROM clause

tried alias but seem's to be not working.

Specs: Server version: 5.5.35-0ubuntu0.12.04.2 (Ubuntu), MYISAM engine, no primary key

user2630309
  • 1
  • 2
  • possible duplicate of [How can I remove duplicate rows?](http://stackoverflow.com/questions/18932/how-can-i-remove-duplicate-rows) – neel shah Mar 19 '14 at 13:52
  • rowid doesn't seems to be working, it says ERROR 1054 (42S22): Unknown column 'rowid' in 'field list' – user2630309 Mar 19 '14 at 14:07

1 Answers1

0

Try:

ALTER IGNORE TABLE Employees 
ADD primary key(`Empid`, `Name`, `Did`, `Salary`);
ALTER IGNORE TABLE Employees DROP PRIMARY KEY;

Working demo (on version 5.5.32): http://sqlfiddle.com/#!2/b22021/3

krokodilko
  • 35,300
  • 7
  • 55
  • 79
  • Thanks a ton, it's working. i have been trying using some rownum concept @rownum. – user2630309 Mar 19 '14 at 14:20
  • Rownum concept cannot work, since MySql does not provide unique rownums like Oracle's `rowid` or `ctid` in PostGreSQL. Also a concept using @rownum session variable is hard to implement in delete, I don't know any solution. Actually working solutions in MySql are based on some unique column (primary key). – krokodilko Mar 19 '14 at 14:30