Add auto-computed column in SQL

Question

I'm trying to add an auto-computed column in my SQL table, which should be easy enough. However, I keep getting an error message saying there's something wrong with the syntax beginning with the word "AS" in the input below:

ALTER TABLE users ADD column_1 AS numerator / denominator;

The table "users" already exists, as do the columns "numerator" and "denominator". Any ideas?

***SQL*** is just the *Structured Query Language* - a language used by many database systems, but not a a database product... many things are vendor-specific - so we really need to know what **database system** (and which version) you're using (please update tags accordingly).... — marc_s, Jun 12 '14 at 21:06

score 2 · Answer 1 · answered Jun 12 '14 at 21:08

2

We can't know for sure until you tell us what database system you are using, but most systems require parenthesis around your statement.

ALTER TABLE users ADD column_1 AS (numerator / denominator);

answered Jun 12 '14 at 21:08

Vulcronos

3,428
3
16
24

score 0 · Accepted Answer · answered Jun 12 '14 at 21:38

AFAIK. MySQL doesn't have support for built-in computed column like in SQL Server. With that, you have two options.

Add the column column_1 using ALTER statement

ALTER TABLE users ADD column_1 ....;

Then UPDATE it like

UPDATE users SET column_1 = numerator/denominator;

(OR)

CREATE A VIEW like below and then use it per your requirement

CREATE VIEW vw_computed
AS
SELECT col1,col2, numerator/denominator as column_1
FROM users

Select from view

select * from vw_computed

Add auto-computed column in SQL

2 Answers2