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
417 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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

695 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