How to alter the computed column in a table

Question

0.00/5 (No votes)

See more:

Hi All,

I have a table containing two computed columns.
Col1 as (Val1+Val2)
Col2 as (Val3+Val4)

it has result as:

Val1 Val2 Val3 Val4 Col1 Col2
-----------------------------
10 20 30 40 30 70

Now, the requirement is I want change the formula of the computed columns as
Col1 as (Val1+Val3)
Col2 as (Val2+Val4)

But having this should not change the previous data, it should only change the new data.

It should be like:

Val1 Val2 Val3 Val4 Col1 Col2
-------------------------------
10 20 30 40 30 70 (Existing data - should not change)
1 2 3 4 4 6 (should change the data according to the new formula )

Thanks in advance!

What I have tried:

I tried doing it using ALTER table
E.g
ALTER TABLE TEMP
DROP COLUMN Col1

ALTER TABLE TEMP
ADD COLUMN Col1 as (Val1+Val3)

But this is causing changing the previous/existing values as well.

Posted 4-May-17 19:06pm

Swati_g1985

Updated 4-May-17 20:17pm

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

**Nirav Prabtani** · Answer 1 · 2017-05-04T20:17:00

Solution 1

You can create trigger instead of change columns

After insert just update column col1 and col2 respectivly

Val1 + Val3 data into Col1
Val2 + Val4 data into Col2

e.g.

CREATE TRIGGER TRIGGER_NAME ON dbo.TABLENAME
AFTER INSERT AS
DECLARE @REF_ID NVARCHAR(55);

SELECT @REF_ID=i.REF_ID FROM inserted i;

UPDATE TABLENAME SET COL1 = (VAL1 +VAL3),COL2 = (VAL2 +VAL4) WHERE REF_ID = @REF_ID

GO

Note : It is written without testing, You may have to change according to your need

Posted 4-May-17 20:17pm

Nirav Prabtani

Comments

Swati_g1985 5-May-17 2:33am

Hi Nirav,

I tried the same but getting below error
The column "COL1" cannot be modified because it is either a computed column or is the result of a UNION operator.

Nirav Prabtani 5-May-17 2:40am

1) Add new column
2) Update old column value to new one
3) Remove old column

Make sure new column should not be computed

- Create a new Column (unpersisted):
ALTER TABLE TABLE_NAME
ADD COL_1 DATATYPE

UPDATE TABLE_NAME
SET COL_1 = COL1

-- Delete the persisted column
ALTER TABLE TABLE_NAME
DROP COLUMN COL1

like this

Swati_g1985 5-May-17 2:52am

COL1 needs to computed column only as per the existing structure.

CHill60 5-May-17 9:49am

In which case you cannot do what you want to do. Nirav's suggestion to use a Trigger is the only way you can persist historical data whilst changing the calculation.
Why have a computed column at all? Just include the calculation in your SELECT having determined the ID (or other means of identifying existing data, e.g. insert date) and do something like

SELECT Col1 = CASE WHEN ID <= 99 THEN Val1 + Val2 ELSE Val1 + Val3 END ..