Beefy Boxes and Bandwidth Generously Provided by pair Networks
Do you know where your variables are?
 
PerlMonks  

Re: Deleting Old MySql Records With PERL

by perlfan (Priest)
on Jul 13, 2016 at 21:02 UTC ( #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);
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 ();

    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?

      What is the data type of the Posted field ?

      poj

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://1167732]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others having an uproarious good time at the Monastery: (7)
As of 2020-06-01 17:04 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    Do you really want to know if there is extraterrestrial life?



    Results (5 votes). Check out past polls.

    Notices?