CODEWITHSUNDEEP
mysqlsql
user9697902
user9697902

Reputation:

SQL delete a specific row among several identical foreign keys but has the oldest date on it

I need to delete a row among several others that have identical foreign keys, but I need to delete the oldest one.

I don't know the oldest date. Therefore I need to check with all the others rows first I think.

 DELETE FROM SOMEWHERE WHERE PROD_ID = 'me' AND DATE ???;");

Data types are:

INT for foreign key. TIMESTAMP for date(to select the oldest date)

I'm using MYSQL.

Upvotes: 0

Views: 45

Answers (3)

Bryan Reis
Bryan Reis

Reputation: 31

A subquery in the WHERE clause should work. Try something like:

DELETE FROM SOMEWHERE A WHERE A.PROD_ID = 'me' AND A.DATE = (
   SELECT MIN(B.DATE) FROM SOMEWHERE B WHERE A.PROD_ID = B.PROD_ID)

This has the benefit of working for multiple PROD_IDs in case you are doing a batch of them instead of just the literal 'me'.

Upvotes: 0

cdaiga
cdaiga

Reputation: 4939

Try this:

DELETE A FROM SOMEWHERE A JOIN (SELECT PROD_ID, MIN(DATE) OLDEST_DATE
FROM SOMEWHERE
GROUP BY PROD_ID) B ON A.PROD_ID=B.PROD_ID AND A.DATE=B.OLDEST_DATE
WHERE A.PROD_ID='me';

See a working DEMO on SQL Fiddle.

Upvotes: 0

pr1nc3
pr1nc3

Reputation: 8338

DELETE FROM SOMEWHERE WHERE PROD_ID = 'me' ORDER BY DATE DESC LIMIT 1

This will get you the oldest date and delete it. You sort it based on your date field, in descending order and you just delete the first (oldest).

Upvotes: 2

Related Questions