asked on

Select Update question

Hi;
I have a query which should fine players with 0 times playing also count(t.id_user) as loginTimes, should be 0. and max(t.end_time) as lastGameDate should be NULL but no matter what I do with it I can not get it to work and still brings max(t.end_time) as lastGameDate which is not NULL.
Is there anyway that I can fix this to achieve the results and how I can rewrite to to update the u.play_chips to 0 also set uplayed accoutn balances to 0. I really appreciate all the help as I can get because I know I can not get it further than this.
Thanks a LOT

select 
u.userid, 
u.play_chips, 
u.reg_timestamp,
count(t.id_user) as loginTimes, 
max(t.end_time)  as lastGameDate
from t_user u
left outer join t_login_session_live t on t.id_user = u.id
where  
(u.reg_timestamp
between '2010-04-01 00:00:00' and '2010-12-29 23:59:59') 
group by u.userid

Open in new window

ASKER CERTIFIED SOLUTION

Om Prakash

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

stermeau

If you want only users where count(t.id_user) = 0 and max(t.end_time) is null, you need to add it to the where clause
---
where
(u.reg_timestamp
between '2010-04-01 00:00:00' and '2010-12-29 23:59:59')
and count(t.id_user) = 0
and max(t.end_time) is null
---

If this query returns what you want, to update just replace the "select ... from" part by update and set the calues of the columns

update t_user u
left outer join t_login_session_live t on t.id_user = u.id
set u.play_chips=0, uplayed=0
where
(u.reg_timestamp
between '2010-04-01 00:00:00' and '2010-12-29 23:59:59')
and count(t.id_user) = 0
and max(t.end_time) is null