lalthung lalthung - 4 months ago 12
SQL Question

query to delete if in table one created is older than 31 days

I have 2 tables one is

posts
and other one is
recent_viewed
both has date as current_timestamp

(posts) has created

and

(recent_viewed) has date

What I wish is to delete all records from (recent_viewed) by comparing in (posts) table if created date is older than 31 days then delete those postID from recent_viewed table

schema

--
-- Table structure for table `recent_viewed`
--

CREATE TABLE IF NOT EXISTS `recent_viewed` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`postID` int(11) NOT NULL,
`memberID` int(11) NOT NULL,
`date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Dumping data for table `recent_viewed`
--

--
-- Table structure for table `posts`
--

CREATE TABLE IF NOT EXISTS `posts` (
`postID` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(70) NOT NULL,
`name` varchar(32) NOT NULL,
`city` varchar(32) NOT NULL,
`created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`postID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Dumping data for table `posts`
--


PS: I'm not good at it that is why i am asking for this help would be very thankful to you.

thanks

Answer

This is not an efficient solution, but a simple one which would definitely helps you :

delete from recent_viewed where postID in (
select postID from posts where abs(datediff(CURRENT_TIMESTAMP,posts.created)) > 31);
Comments