?
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
?
418 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
Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

 

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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

In this article, we’ll look at how to deploy ProxySQL.
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
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

752 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