Mysql Insert data to new column with select query

Question

I have table structure like this

id     start_date      during
1      2018-01-01       2
2      2018-01-02       3

And then I would like to add a new column named end_date using my query

SELECT 'start_date' + INTERVAL ('during')day as 'end_date'

then the result become end_date

end_date
2018-01-03
2018-01-05

I would like to add a new column in my table named end_date and then insert the data by query before. After I create the new column in my table, I did the query below to insert

INSERT INTO table_name(end_date) 
SELECT 'start_date' + INTERVAL ('during')day

but the table gonna like this

id     start_date      during    end_date
1      2018-01-01       2         null
2      2018-01-02       3         null
3         null          null     2018-01-03 
4         null          null     2018-01-05

What you want is not INSERT new rows, but to UPDATE existing ones by adding values to a new column... — Usagi Miyamoto
– Usagi Miyamoto, Commented Mar 7, 2019 at 15:00

nowox · Accepted Answer · 2019-03-07 15:00:57Z

1

First you need to add a new column to your table:

ALTER TABLE `table_name`.`new_table` 
ADD COLUMN `end_date` DATE NULL AFTER `during`;

Then you can apply your insert:

UPDATE table_name SET end_date = start_date + INTERVAL(during) DAY;

But if during is always defined from start_date and during you could create a virtual column:

ALTER TABLE `table_name`.`new_table` 
ADD COLUMN `end_date` `end_date` DATE GENERATED ALWAYS AS (
    start_date + INTERVAL(during) DAY) VIRTUAL ;

answered Mar 7, 2019 at 15:00

nowox

29.7k49 gold badges179 silver badges361 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ghozally Over a year ago

Thanks, i works sir :) I am stuck because I think the query that should to do is Insert.

Collectives™ on Stack Overflow

Mysql Insert data to new column with select query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related