-2

I have a sql table in a MySQL with the following records:

+------+----------+
| user |   dob    |  
+------+----------+
| john | 1/10/96  | 
| jane | 3/4/97   | 
| jill | 1/8/96   | 
| jack | 2/9/00   |
| jane | 12/14/07 | 
| john | 1/11/98  | 
+------+----------+

Here's the command I want to execute:

delete all users with dob < 1/1/00 but keep a user if there exists another user with the same name whose dob >= 1/1/00.

In this example, the johns and jill would be deleted and the janes and jack would stay.

How do I translate the above command into sql?

Brinley
  • 591
  • 2
  • 14
  • 26
  • It wont delete users where dob >= 1/1/00, you can compile now by using delete command. Try and compile a delete script and we will enhance if needed – Ven Jun 29 '17 at 14:52
  • Are you saving dob as String ? MYSQL by default use specific format for dates, so it won't be saved as you wrote in question : refer : https://stackoverflow.com/questions/14051057/mysql-date-function-not-working-for-less-than – Aditya T Jun 29 '17 at 15:03

3 Answers3

1

Try this:

DELETE FROM Users WHERE dob <= '1/1/00'
Rekcs
  • 869
  • 1
  • 7
  • 23
  • cant use = , coz it will delete rows with = '1/1/00'. OP doesn't want that it has to be – Ven Jun 29 '17 at 14:54
1

WIth the help of Delete with Join in MySQL

I made 

DELETE FROM table_name a
    INNER JOIN (SELECT count(name) as dupesPostMillenia, name 
                FROM table_name 
                WHERE dob>'1/1/00' 
                GROUP BY name ) b on a.name=b.name
    WHERE a.dob < '1/1/00' and b.dupesPostMillenia=0

I think this might help.

Ricardo Paixao
  • 324
  • 2
  • 10
0
DELETE FROM users WHERE dob < '2000-01-01'

Please note : I am considering that you are actually saving date in datetime format in MYSQL. Also, you can use various formats for date in MYSQL. Refer : https://stackoverflow.com/a/14051310/6385845

Aditya T
  • 1,566
  • 2
  • 13
  • 26