Complex Mysql query help

Hi,

I have two tables:
users and active

The active table contains 3 fields:
ID, UID, TIME

users table contains:
ID, user, AC

example:
users:
1 xxx 0
2 xxa 3
3 asdas 5
4 sdss 1

active:
1 1 55545412
2 1 45454154
3 1 45541212
4 2 54541245
5 2 54541541



i need to increase the AC by 1, if (UNIX_TIMESTAMP()-active.TIME)<3600, and only once for each uid.

The system i've been using is somewhere like:

$r=mysql_query("SELECT DISTINCT uid FROM active WHERE  (UNIX_TIMESTAMP()-active.TIME)<3600");
while($x=mysql_fetch_array($r)){
mysql_query("Update users SET ac=ac+1 WHERE id=$x[uid]");
}

Code (markup):

But i want to use only one query, for performance reasons...
Something like:

Update users SET ac=ac+1 WHERE (UNIX_TIMESTAMP()-active.TIME)<3600 AND users.id=active.uid

Code (markup):

but with DISTINCT uid into the active table.
Can anyone help?


Thanks

 

Try this query on your test data. I have not tested this

UPDATE users SET ac = ac+1
WHERE id IN (SELECT DISTINCT uid FROM active WHERE  (UNIX_TIMESTAMP()-active.TIME)<3600 )

Code (markup):