MYSQL How to add virtual column into existing table-CodePudding

I have this 'data' table below with columns 'INITIAL','BETWIXT','ENDING' with values inserted;

SET SQL_SAFE_UPDATES = 0;

CREATE TABLE data (
    INITIAL INT NOT NULL,
    BETWIXT INT NOT NULL,
    ENDING VARCHAR(20)
);

INSERT 
    INTO data(data.INITIAL,data.BETWIXT,data.ENDING) 
    VALUES
    (30,40,"N0"), 
    (60,80,"N1"), 
    (90,120,"N2"),
    (120,160,"N3"),
    (150,200,"N4"); 

SELECT DISTINCT ENDING AS "HI"
    FROM data
    WHERE data.INITIAL BETWEEN 60 AND 150;

But I'm approaching to insert the "HI" virtual column into the main table 'data' so it can be visualize as

INITIAL  BETWIXT  ENDING  HI 

30       40      N0     N1

60       80      N1     N2

90       120     N2     N3

120      160     N3     N4
 
150      200     N4

Instead of just single virtual column, how do I write a code in order to construct the above?.

CodePudding user response：

Are you looking to get next value of ending column in HI column. If so, LEAD can be used.

select initial,BETWIXT,ending,lead(ending) over (order by initial) hi from data;

Refer fiddle here.

CodePudding user response：

your output example is not correct coz for the 1st row you should get a null value, not 'N1'. Regarding your question, just list all columns in your query:

select t.initial,
t.betwixt,
t.ending,
case when t.initial between 60 and 150 then t.ending else NULL end as 'HI'
from data t;

If you really need the output with a gap of 1 step, then see the code from @Pankaj post