?
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
?
421 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
  • 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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 

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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
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
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
Course of the Month16 days, 10 hours left to enroll

864 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