Home Update using subquery
Reply: 1

Update using subquery

Samila
1#
Samila Published in 2017-12-07 06:23:04Z

I have SELECT query as below that need to be inserted into table wifi_user_analytic_dy.

SELECT
  date(acctstarttime)
, round(avg(acctsessiontime),2)average_connected_ap 
, calledstationid 
FROM ( SELECT acctstarttime, acctsessiontime, calledstationid 
   FROM wifi_radacct 
   WHERE acctstoptime <> '' 
   and date(acctstarttime)= date_trunc('day', now()) -'1 day'::interval 
 )a 
group by calledstationid, date(acctstarttime)

Sample result based on the query :

| date       | average_connected_ap | calledstationid |
| 2017-12-06 | 1000                 | publica         |
| 2017-12-06 |  800                 | tumis           |
| 2017-12-06 |  500                 | penyet          |

The above query is going to find average connection base on date and ap and update into table wifi_user_analytic_dy. Below is the expected result inside table wifi_user_analytic_dy.

| date       |  ap      | average_connected_duration_ap |
| 2017-12-06 | publica  |     1000                      |
| 2017-12-06 | tumis    |      800                      |
| 2017-12-06 |  penyet  |      500                      |

I am trying copy average_connected_ap data from the SELECT query above and update the table wifi_user_analytic_dy at column average_connected_duration_ap. The UPDATE query below is not produced the correct result. It is copying the same value to all ap.

How can I correct this update query.

UPDATE wifi_user_analytic_dy B
SET avg_connected_duration_ap = ( select round(avg(wifi_radacct.acctsessiontime),2)
       from wifi_radacct
       where date(acctstarttime)= date_trunc('day', now()) -'1 day'::interval 
       )
FROM wifi_radacct A
WHERE A.calledstationid=B.ap and A.date(acctstarttime)= B.date
;

Hope someone can advise me.

Jasen
2#
Jasen Reply to 2017-12-08 04:38:02Z

you could use a CTE. something like:

WITH C as 
( select 
   D.ap, D.bate,
   (select round(avg(wifi_radacct.acctsessiontime),2)
   from wifi_radacct
   where date(acctstarttime)= date_trunc('day', now()) -'1 day'::interval) as V
  FROM wifi_radacct A
  JOIN wifi_user_analytic_dy D
  ON A.calledstationid=D.ap and A.date(acctstarttime)= D.date
)
UPDATE wifi_user_analytic_dy B
SET avg_connected_duration_ap = V
FROM C where A.calledstationid=C.ap and A.date(acctstarttime)=C.date

but then you don't need the subquery either:

WITH C as 
( select 
   D.ap, D.bate,
   round(avg(A.acctsessiontime),2
   from wifi_radacct
   where date(acctstarttime)= date_trunc('day', now()) -'1 day'::interval) as V
  FROM wifi_radacct A
  JOIN wifi_user_analytic_dy D
  ON A.calledstationid=D.ap and A.date(acctstarttime)= D.date
  GROUP BY D.ap,D.date
)
UPDATE wifi_user_analytic_dy B
SET avg_connected_duration_ap = V
FROM C where A.calledstationid=C.ap and A.date(acctstarttime)=C.date

and possibly don't need the join.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.309594 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO