1

this is my table structure i have duplicate entry i will define duplicate by date (time stamp is in seconds) how can i delete is using sql query ?

database name db_misc table name t_cmd_logs

lid     user    message     date    addedDate

Here is my data:

lid     user       message         date                 addedDate
5   DarkScorpion    -jjjj   2016-08-08 17:23:24     2016-08-08 17:24:01
6   DarkScorpion    -jjjj   2016-08-08 17:23:24     2016-08-08 17:25:01
7   DarkScorpion    -jjjj   2016-08-08 17:23:24     2016-08-08 17:26:01
8   DarkScorpion    -jjjj   2016-08-08 17:23:24     2016-08-08 17:27:02
9   DarkScorpion    -jjjj   2016-08-08 17:23:24     2016-08-08 17:28:01
10  DarkScorpion    -jjjj   2016-08-08 17:23:24     2016-08-08 17:29:01
11  DarkScorpion    -jjjj   2016-08-08 17:23:24     2016-08-08 17:30:01
12  DarkScorpion    -jjjj   2016-08-08 17:23:24     2016-08-08 17:31:01
13  DarkScorpion    -jjjj   2016-08-08 17:23:24     2016-08-08 17:32:01
14  DarkScorpion    -jjjj   2016-08-08 17:23:24     2016-08-08 17:33:01
15  DarkScorpion    -jjjj   2016-08-08 17:23:24     2016-08-08 17:34:01
16  DarkScorpion    -jjjj   2016-08-08 17:23:24     2016-08-08 17:35:01
  • I apologize, I am weak in English.

i found 1 way to copy t_cmd_logs data to t_cmd_log but i have to run this every 1 minute so i have to delete data from t_cmd_logs after copy is there any thing possible i have used 

INSERT INTO t_cmd_log  SELECT * FROM t_cmd_logs WHERE 1 GROUP BY user,message,date ;

DELETE FROM t_cmd_logs;

but in phpmyadmin sql gives me error am i doing it wrong ? please help me

Himesh Patel
  • 21
  • 4
  • it's easier to block duplicates on insertion than fix it afterwards – Ivan Aug 08 '16 at 20:53
  • is that what you're looking for http://stackoverflow.com/questions/15753862/trying-to-delete-duplicate-records-in-sql-but-the-query-is-going-in-an-infinite ? – Ivan Aug 08 '16 at 20:54
  • Your description of your problem -- "duplicate by date (time stamp is in seconds)" -- doesn't obviously match your list of columns. – O. Jones Aug 08 '16 at 20:58

1 Answers1

-1

if you want to delete a row and you know the addedDate;

delete from YOUR_TABLE where your_date_column = '2009-01-01';
Stephen Jackson
  • 260
  • 2
  • 6
  • 20