Solved

how can I delete all records with the same date (by day, no time) that also has the same value for 1 other column.

Posted on 2013-12-21
8
415 Views
Last Modified: 2013-12-27
how can I delete all records with the same date (by day, no time because the time might be different. Actual date value I have: 2013-12-08 15:33:20) that also has the same value for 1 other column. I'm getting duplicates and I want to run that sql command afterwards to fix the issue.

Thanks!
0
Comment
Question by:brihol44
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 39733999
Hi brihol44,

Try this:

DELETE from {mytable}
WHERE date (column) = '23/08/2013'
  AND othercolumn = 'something';

Open in new window


Good Luck,
Kent
0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 39734002
Oh.  The data inside the string should conform to the format that you're using.

Kent
0
 

Author Comment

by:brihol44
ID: 39734040
ok, I need to dynamically pull the date and something value and have mysql work for me not statically place 'something' or the date individually in the column. So if it's the date value wouldn't it be...

DELETE from {mytable}
WHERE date (column) = '%Y-%m-%d'
  AND othercolumn = 'something';

but wouldn't this statement also delete all records?
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

Author Comment

by:brihol44
ID: 39734042
to be clear I just want to leave 1 record but delete all other duplicate records with the same date and othercolumn value = the same.
0
 
LVL 45

Assisted Solution

by:Kent Olsen
Kent Olsen earned 400 total points
ID: 39734310
Ah.  Ok.

If we assume that the timestamp is different, even if only my a millisecond, this should do fine:

DELETE FROM {mytable}
WHERE date (column) = '23/08/2013'
  AND othercolumn = 'something'
  AND column <> (SELECT min(column) from {mytable} WHERE othercolumn='something');

Open in new window


If you have a newer version of SQL SERVER you can also use ROW_NUMBER to do the same thing

WITH t0
AS
(
  SELECT date (column), 
    row_number () over (partition by date (column) order by date (column)) RN
  FROM {mytable}
)
DELETE FROM t0 WHERE RN > 1;

Open in new window



Good Luck,
Kent
0
 

Accepted Solution

by:
brihol44 earned 0 total points
ID: 39735309
Sort of but this is what I did myself and is what I really needed...

<cfquery datasource="#request.dsn#" username="#request.dsnUsername#" password="#request.dsnPassword#">
DELETE FROM video_searches_copy
WHERE record_id NOT IN
(
    SELECT minID
    FROM
    (
        SELECT video_id, DATE_FORMAT(search_request_date, '%Y-%m-%d') as mydate, MIN(record_id) minID
        FROM video_searches_copy
        GROUP BY video_id, mydate
    ) x
);
</cfquery>
0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 39735322
Ah.  Even easier.  (And you can use the primary_key in most any filtered delete.)  From the earlier description it appeared that the data didn't contain a primary key.


Kent
0
 

Author Closing Comment

by:brihol44
ID: 39741503
I guess i wasn't clear enough but the suggestion got me thinking in the right direction.
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question