So i have a social networking database. i now want to delete any messages written by a user that was made visable to the public after 1 year.
my messages table has a coloum called is_public. With the value '0' meaning it is public to anyone.
So i understand a Delete statement as such, would work.
DELETE FROM Message WHERE is_public=0.
However i need it to only delete for messages that are one year old. Now my messages table has a date/time coloum with the values in the following format :2011-03-12 10:10:48.
I understand i would have to add another part to the WHERE CLAUSE, but how would i do it so only 1 year old messages are deleted?
Also once i have this, i only want this DELETE statement to work if the user is from a particular country and was under the age of 21 at the time of posting the message. the users age and country are recorded in my USER table. and the user is linked with a foreign key relation to my messages table under the owner_id coloum. so by using this relation i can get the users age and country.
the messages coloum looks like this to make it a bit easier.
message_id, owner_id, subject, body, date_time_posted, is_public.
1 1 Party at my place 2nite 7 pm till l8!!!! CU there!!! 2011-02-15 15:06:28 1
2 1 COMPSCI280 Just enrolled there, seems a cool course 2011-02-15 15:20:35 1
3 1 Hangover! Maaaan, that party rocked! 2011-02-16 11:45:15 0
So basically, for example. I want to delete any messages one year after they were published. If the message was public, the person was under 21 and he was from New Zealand.
I think this would require a procedure method due to the many statements required?