[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 295
  • Last Modified:

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

0
babak62
Asked:
babak62
1 Solution
 
Om PrakashCommented:
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

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now