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

babak62Asked:
Who is Participating?
 
Om PrakashConnect With a Mentor Commented:
add having clause to your query. Example
elect
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
having count(t.id_user) = 0 and max(t.end_time) is not null
0
 
stermeauCommented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.