0

I have the following table called tbl_test in a MySQL database:

+------+----------+
| 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  | 
+------+----------+

In English, here's what I want to accomplish:

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.

I've come up with the following sql command to do this:

DELETE from tbl_test
WHERE dob < '2000-01-01'
AND name NOT IN (SELECT name from tbl_test WHERE dob >= '2000-01-01')

And MySQL throws this error: You can't specify target table 'tbl_test' for update in FROM clause.

How can I modify the above command to make it legal?

Brinley
  • 591
  • 2
  • 14
  • 26

3 Answers3

0

You could use this trick 

  DELETE from tbl_test
  WHERE dob < '2000-01-01'
  AND name NOT IN ( select T.name 
                        from (SELECT name 
                              from tbl_test WHERE dob >= '2000-01-01') T)

mysql don't allow delete the same table you use for search but you can force to create dinamically a temporary table so this can go over this limit

ScaisEdge
  • 131,976
  • 10
  • 91
  • 107
0

The normal fix to this is to use left join:

DELETE t
    FROM tbl_test t LEFT JOIN
         tbl_test tt
         ON t.name = tt.name and tt.dob >= '2000-01-01'
    WHERE t.dob < '2000-01-01' AND
          tt.name IS NULL;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0
DELETE from user
WHERE name IN (Select name from user where dob < 946684800)
AND name not in (Select name from user where dob > 946684800);

Change the number to date I was limited in resources at the time.