How to remove duplicate rows in a SQL table With no id and 2 row has ids

Question

I got double row in my sql database and I don't know how to remove them.

(my sql table)

  date        quantity  city   supervisor  bonus  empnum
  ----------  --------  -----  ----------  -----  ------
  2016-05-01       46   laval  mlasselin      10     837
  2016-05-01       46   laval  mlasselin      10     837
  2016-05-01       46   laval  mlasselin      10     837
  2016-05-01       43   laval  mlasselin      25     880

I want to use a SQL query to delete extra row only keep one. I have this kind of row all my database I need a sql query to remove them all and keep only one date and one empnum

I dont have id like the other standart table
Q: What SQL query can I use to remove the duplicate rows?

Delete both of them and then insert just the one? Perhaps adding a unique key to the table will prevent this — Ed Heal, May 18 '16 at 14:01
Do you have any other fields in the table? Do you have a primary key? — Kickstart, May 18 '16 at 14:04
empnum is unique to the employe and date if the date of the sale — Gabriel Therrien, May 18 '16 at 14:07
Than I suggest you add an auto increment primary key to the table. While there are ways to delete all the rows and insert one unique one back they are messy, and having a unique key makes things FAR easier — Kickstart, May 19 '16 at 08:08

score 0 · Answer 1 · answered May 18 '16 at 14:05

0

DELETE FROM foo WHERE a=12345 LIMIT 1;

Find something unique that identifies the lines you want to delete and then use the LIMIT clause.

answered May 18 '16 at 14:05

David Stokes

120
3

can I use the date and the empnum as unique? – Gabriel Therrien May 18 '16 at 14:09

How to remove duplicate rows in a SQL table With no id and 2 row has ids

1 Answers1