SQL Guru required!

[OA01]

I am trying to run a CRON job containing the following commands:

mysql_query("update pireps set rawdata='', log='', route='' where submitdate < DATE_SUB(NOW(), INTERVAL 3 MONTH)") or die(mysql_error());
mysql_query("update adminlog set id='', pilotid='', datestamp='', message='' where datestamp < DATE_SUB(NOW(), INTERVAL 1 MONTH)") or die(mysql_error());
mysql_query("update pirepcomments set id='', pirepid='', pilotid='', comment='', postdate='' where postdate < DATE_SUB(NOW(), INTERVAL 3 MONTH)") or die(mysql_error());

The first two lines appear to run OK. However the last line generates the following error:

Cannot add or update a child row: a foreign key

constraint fails (`orangea1_acars`.`pirepcomments`, CONSTRAINT

`pirepcomments_ibfk_1` FOREIGN KEY (`pirepid`) REFERENCES `pireps` (`pirepid`) ON

DELETE CASCADE ON UPDATE CASCADE)

All suggestions welcomed

[Tom]

Are you actually just updating the fields to '' or have you removed what goes in there? If you're just emptying it, why not DELETE?

[OA01]

I am trying to delete the contents of those fields. How would I use DELETE?

[freshJet]

What are you actually trying to do here? Delete all comments older than 3 months?

In that case, it would be:

DELETE FROM pirepcomments WHERE postdate < DATE_SUB(NOW(), INTERVAL 3 MONTH)

[Tom]

(And you should probably do the same for the other two queries if that is what you're trying to achieve)

[OA01]

I'll give it a try, THANKS guys

[mseiwald]

You definately shouldnt do that with the pireps query or you lose all pireps older than 3 months.

[Tom]

What are you actually trying to achieve here? Why are you clearing out these particular fields?

[mseiwald]

its probably related to the out of memory error: http://forum.phpvms.net/topic/8785-out-of-memory/

[OA01]

mseiwald is correct. I haven't experienced the problem so far, I'm just trying to keep the DB size to a minimum.

[Tom]

Right ok, updating is probably the best bet then, don't do it on any id fields though because they have relationships to other tables (plus emptying a small number from each row saves negligible amounts of space - go for the bulky useless stuff)

[Strider]

If you are worried about an OOM error, then you have two options. First delete some files such as your fleet files and host them else where and link them to your fleet page. Or you could upgrade your hosting package, to increase the space.

[OA01]

First delete some files such as your fleet files and host them else where and link them to your fleet page. Or you could upgrade your hosting package, to increase the space.

Physical space isn't an issue, I have unlimited. I am just performing good housekeeping! The following works a treat.

mysql_query("update pireps set rawdata='', log='', route='' where submitdate < DATE_SUB(NOW(), INTERVAL 3 MONTH)") or die(mysql_error());
mysql_query("update adminlog set id='', pilotid='', datestamp='', message='' where datestamp < DATE_SUB(NOW(), INTERVAL 1 MONTH)") or die(mysql_error());
mysql_query("DELETE FROM pirepcomments WHERE postdate < DATE_SUB(NOW(), INTERVAL 2 MONTH)") or die(mysql_error());

[Strider]

You should just upgrade your hosting, as deleting pireps is only a temporary solution.