Beefy Boxes and Bandwidth Generously Provided by pair Networks
Your skill will accomplish
what the force of many cannot
 
PerlMonks  

Re: Deleting Old MySql Records With PERL

by perlfan (Vicar)
on Jul 13, 2016 at 21:02 UTC ( [id://1167732]=note: print w/replies, xml ) Need Help??


in reply to Deleting Old MySql Records With PERL

If your the column with your date in it is really called date, you're gonna have a bad time. Try putting the column name date in back ticks:
(untested, but exemplifies what I am saying): 
DELETE FROM table WHERE `date` < DATE_SUB(NOW(), INTERVAL 30 DAY);

[download]
Furthermore, make sure that column `date` is of the right column type (DATE or DATETIME). Finally, I test my SQL using the mysql client; if it succeeds there, it should succeed in your Perl script.

Replies are listed 'Best First'.
Re^2: Deleting Old MySql Records With PERL
by Milti (Sexton) on Jul 14, 2016 at 16:00 UTC

    I tried the suggested code in my program and it didn't work. I have changed the Date column name to Posted and am using this code

    my $dbh = DBI->connect('dbi:mysql:owner_db','searcher','searcherpasswd
+')
or die "Connection Error: $DBI::errstr\n";
$sth = $dbh->prepare ("DELETE FROM my table WHERE POSTED < DATE_SUB(NO
+W(), INTERVAL 30 DAY)");
$sth->execute ();

    [download]

    I have also tried the same code with the mysql client. In both cases the 30 DAY interval is ignored and everything is deleted.

    Any suggestions?

[reply]
[d/l]

      What is the data type of the Posted field ?

      poj
[reply]

In Section Seekers of Perl Wisdom

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?

www.com | www.net | www.org
Node Status?
node history
Node Type: note [id://1167732]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? • Last hour • Other CB clients
Other Users?
Others contemplating the Monastery: (5)
As of 2024-04-25 14:36 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found