Help with mysql sub-query

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?

 

What's the error that MySQL spits out?

 

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT threadID

 

mysql 4 cannot accept subqueries ... so ... you need to rewrite your query to use temporary tables ... or any other way ...


Regards

 

How can I do that?

 

You need to do 2 querys.

first SELECT ID FROM items i group by i.ID having max(i.dateCreated) < CURDATE() - INTERVAL 14 DAY ;

second: update table t set t.status = 23 where t.status = 5 AND t.ID IN #RESULTFIRST#

Or try this:

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

Regards