CODEWITHSUNDEEP
sqlpostgresql
kee
kee

Reputation: 11629

Updating a field in a table with a number aggregated from other table

I have a log table with web log entries which have a session ID. I also have a session table summarizing sessions from the previous table. So I have to run some update SQL statement but I don't get how to construct a SQL statement for a field named "session_length". In this field I hope to assign the number of events in that particular session.

Let's say I have the following log table:

| Session ID | Timestamp | Action | ...
| 1          | 00:00:00  | get | ...
| 2          | 00:00:00  | get | ...
| 1          | 00:00:01  | get | ...
| 1          | 00:00:02  | get | ...
| 2          | 00:00:02  | get | ...

In the session table, I would like to have the following values for session_length field:

| Session ID | session_length | ...
| 1          | 3              | ...
| 2          | 2              | ...

I am not sure whether this can be done by a single query but I would like to see if this can be done by a single SQL query using update. In particular, I am using PostgresSQL in AWS RedShift.

Upvotes: 0

Views: 202

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You can do this with a correlated subquery in the update statement:

update sessions
    set session_length = (select count(*)
                          from log
                          where log.sessionid = sessions.sessionid
                         )

Upvotes: 1

Related Questions