View Full Version : Help with mysql sub-query
04-06-2006, 11:23 AM
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?
04-06-2006, 01:11 PM
Is there an error code?
04-06-2006, 01:30 PM
The error code is 1064
04-06-2006, 01:36 PM
Seems you may have used a reserved word : http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html
04-06-2006, 01:50 PM
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
04-06-2006, 02:10 PM
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:
04-06-2006, 02:16 PM
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.
04-06-2006, 02:18 PM
Can't the cron job run a full php script?
04-06-2006, 02:21 PM
It seems as if you've got an idea. I don't know is there a place where I can check that?
04-06-2006, 02:24 PM
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).
vBulletin® v3.8.5, Copyright ©2000-2013, Jelsoft Enterprises Ltd.