Help with mysql sub-query

[gntombel]

I have this query:

update table t set t.status = 23
where t.status = 5 AND t.ID IN( SELECT ID
FROM items i
group by i.ID
having max(i.dateCreated) < CURDATE() - INTERVAL 14 DAY );

The query does work properly with mysql 5. How can I change it so that it runs in version 4.0.x of mysql?

[webado]

Is there an error code?

[gntombel]

The error code is 1064

[webado]

Seems you may have used a reserved word : http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html

[gntombel]

The query does run properly on he latest versions(4.1.x -> 5.x). The problem is that I'm using version 4.0.x and it's beyond my reaches to upgrade it so my request was to get help in tranforming this a different query which can achieve the same result. ie. using joins.

I did look at joins as a solution but I could only find a solution where I work with simple update ie.

update table1,table2 set table1.x = "", table2.x2 = "" where
table1 left join table2 on table1.something = table2.something

So I was looking at getting something similar

[webado]

Sorry, I don't know. Maybe somebody else can figure it out.

If I understand correctly you want to modify the value of the status field in table t for all records of t where the value of the file ID is among the records from table i for which the field dateCreated) < (CURDATE() - INTERVAL 14 DAY ) .


In theory at least I would do it by getting the records from table i that correspond to that criterion dateCreated) < (CURDATE() - INTERVAL 14 DAY ) and then loop through all of them and access table t for the given value of the field ID and perform the update of the field status on each of the records in table t with the same value of the field ID.

But I'd have to figure out the exact syntax of both the php and the MySql query required and that's where I am worse than rusty :oops:

[gntombel]

I think I was gonna be able to work around it if I was going to use code but I need to run this command on a shell script where I will use cron(linux scheduler) to run it periodically that's why I want a single line query.

[webado]

Can't the cron job run a full php script?

http://www.webmasterworld.com/forum88/6102.htm

[gntombel]

It seems as if you've got an idea. I don't know is there a place where I can check that?

[webado]

Well a Google search on "cron job php script" brings out lots of sites with help.

Here's one: http://www.htmlcenter.com/tutorials/tutorials.cfm/155/PHP/

In Cpanel you can usually set cron jobs up (though I've never done so myself yet).