Update a column value within a SELECT query

Question

I have a complicated SQL question.

Can we update a column within a SELECT query? Example:

Consider this table:

    |ID   |SeenAt  |
    ----------------
    |1    |20      |
    |1    |21      |
    |1    |22      |
    |2    |70      |
    |2    |80      |

I want a SELECT Query that gives for each ID when was it seen for the first time. And when did it seen 'again':

    |ID   |Start  |End  |
    ---------------------
    |1    |20     |21   |
    |1    |20     |22   |
    |1    |20     |22   |
    |2    |70     |80   |
    |2    |70     |80   |

First, both columns Start and End would have the same value, but when a second row with the same ID is seen we need to update its predecessor to give End the new SeenAt value. I succeeded to create the Start column, I give the minimum SeenAt value per ID to all IDs. But I can't find a way to update the End column everytime.

Don't mind the doubles, I have other columns that change in every new row

Also, I am working in Impala but I can use Oracle.

I hope that I have been clear enough. Thank you

You need the `min()` and `max()` analytic functions, by the looks of it. Or possibly the [tabibitosan](https://stackoverflow.com/a/48210767/4479309) method, if you're looking for consecutive groups of the id (e.g. what if a new row for id = 1 was added with a seenat of 30? Would that be a group on its own, or would it belong with the first group, with min = 20 and max = 30? — Boneist, Jul 30 '19 at 14:51
are you sure about the values in the output for `End`?I suppose they should be `20,21,22,70,80`,aren't they? — Barbaros Özhan, Jul 30 '19 at 14:59
End should contain the start value of the new row with the same ID. — Haha, Jul 30 '19 at 15:04

score 1 · Answer 1 · answered Jul 30 '19 at 14:49

Start is easy just the MIN of the GROUP

End you need to find the MIN after the SeenAt and in case you don't find it then the current SeenAt

SQL DEMO

SELECT "ID", 
        (SELECT MIN("SeenAt")
         FROM Table1 t2
         WHERE t1."ID" = t2."ID") as "Start",
        COALESCE(
                 (SELECT MIN("SeenAt")
                  FROM Table1 t2
                  WHERE t1."ID" = t2."ID"
                  AND t1."SeenAt" < t2."SeenAt")
                 , t1."SeenAt"
                ) as End

FROM Table1 t1

OUTPUT

| ID | START | END |
|----|-------|-----|
|  1 |    20 |  21 |
|  1 |    20 |  22 |
|  1 |    20 |  22 |
|  2 |    70 |  80 |
|  2 |    70 |  80 |

Barbaros Özhan · Answer 2 · 2019-07-30T15:05:51.980

1

you seem to need min() analytic function with a self-join:

select distinct t1.ID,
       min(t1.SeenAt) over (partition by t1.ID order by t1.ID) as "Start",
       t2.SeenAt as "End"
  from tab t1
  join tab t2 on t1.ID=t2.ID and t1.SeenAt<=t2.SeenAt
 order by t2.SeenAt;

Demo

edited Jul 30 '19 at 15:05

answered Jul 30 '19 at 14:52

Barbaros Özhan

59,113
10
31
55

score 1 · Accepted Answer · answered Jul 30 '19 at 14:55

1

You could use lead() and nvl():

select id, min(seenat) over (partition by id) seen_start,
       nvl(lead(seenat) over (partition by id order by seenat), seenat) seen_end
  from t

^demo

answered Jul 30 '19 at 14:55

Ponder Stibbons

14,723
2
21
24

Update a column value within a SELECT query

3 Answers3