CODEWITHSUNDEEP
sql
CMHedborg
CMHedborg

Reputation: 83

Create a new column based on other columns

I've managed to create the following column in a new table: 

CREATE TABLE t_issue_dates as

SELECT issue_d,

cast(substr(issue_d,5,4) as numeric) as issue_year
FROM myDB

(The code recodes a year-month variable to a year-only variable)

However, i can't add this variable to my existing table "myDB". I've tried using the:

ALTER TABLE myDB ADD v_year - command, but i can't manage to get it right.

Does anyone have an idea how i add the above variable to the "original" table myDB?

Thank you!

Upvotes: 8

Views: 52551

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270873

First, many databases support computed or generated columns. That means that you can add a virtual column to the database by doing:

alter table t_issue_dates add issue_year as (cast(substr(issue_d, 5, 4) as numeric));

I recommend this approach because issue_year is always up-to-date.

Second, you can do this as an actual column, but the value can get out-of-date and needs to be re-calculated for each inserted/updated row:

alter table t_issue_dates add issue_year numeric;

update t_issue_dates
    set issue_year = cast(substr(issue_d, 5, 4) as numeric);

For the record, I would use int rather than numeric.

Upvotes: 9

Ven
Ven

Reputation: 2014

Assuming you are using MSSQL. It would be worth of reading documentation or a simple google on how to insert

Adding column :

Alter table t_issue_dates 
 Add V_year Int

Next step: This will only insert data for this particular column.

Insert into t_issue_dates  (v_year)
SELECT 
cast(substr(issue_d,5,4) as numeric) 
FROM myDB

Upvotes: 0

Related Questions