[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
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
Medium Priority
?
420 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 46

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 46

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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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 46

Assisted Solution

by:Kent Olsen
Kent Olsen earned 1200 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 46

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

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

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

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

656 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