I have a table
student_id INTEGER PRIMARY KEY AUTOINCREMENT, name varchar(255) NOT NULL, surname varchar(255) NOT NULL, dateOfBirth DATE NOT NULL, yearEnrolled YEAR NOT NULL);
with values
INSERT INTO student (name, surname, dateOfbirth, yearEnrolled) VALUES ("Walter", "Ananas","1998-05-25",2020), ("Wander", "Ananas","1998-07-24",2019), ("Andrew", "Maxwell","1999-09-25",2020), ("Philip", "Plein","1997-05-15",2020), ("Wayne", "Johnson","1998-02-15",2020), ("Chalton", "Cole","1998-07-29",2018), ("Moise", "Kevin","2000-11-25",2021), ("Wiktor", "Watermelon","1998-12-15",2021), ("Jean-Pierre", "Care","1998-01-22",2020), ("Anthony", "Martial","1996-05-13",2018), ("Wilt", "Chamberlain","1997-05-25",2017), ("Donnavan", "Wiston","1995-05-15",2017), ("Lothar", "Mathews","1999-03-05",2020), ("Ant", "Fibonacci","2001-11-13",2021), ("Kelton", "Mansour","1997-12-15",2021), ("Jean-Pierre", "Cordone","1996-11-22",2020), ("Anthony", "Morinson","1997-05-18",2018), ("Balwain", "Chamberlain","1999-11-25",2018), ("Donnavan", "Konwar","1997-05-22",2017), ("Lothar", "Ans","1995-03-16",2016), ("Walter", "Doile","1994-05-17",2017);
I would like to delete the row with the oldest student.
I am able to get the ages with the following command but somehow I cant combine it with the DELETE stament
DELETE FROM student WHERE SELECT dateOfBirth, DATE('now')- dateOfBirth as age FROM student;
I am using SQL lite
Advertisement
Answer
This would remove the first of the MAX
result if there are some with the same age:
DELETE FROM student WHERE student_id = SELECT student_id , MAX(age) FROM ( SELECT student_id, cast(strftime('%Y.%m%d', 'now') - strftime('%Y.%m%d', dateOfBirth) as int) as age FROM student )
This would remove all with the MAX
age:
DELETE FROM student WHERE cast(strftime('%Y.%m%d', 'now') - strftime('%Y.%m%d', dateOfBirth) as int) = SELECT MAX(age) FROM ( SELECT cast(strftime('%Y.%m%d', 'now') - strftime('%Y.%m%d', dateOfBirth) as int) as age FROM student )