Adding in SQL Column that Totals Other Column Values

Question

Trying to add a new column into pre-existing table with the following command:

ALTER TABLE randomquestions ADD Total AS R1_Number + R2_Number + R3_Number + R4_Number PERSISTED

I get this error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS R1_Number + R2_Number + R3_Number + R4_Number PERSISTED' at line 2

Can anyone tell me where I went wrong with the syntax?

Please decide #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server Microsoft SQL Server or MySQL. — Lukasz Szozda
– Lukasz Szozda, Commented May 17, 2016 at 15:23
You're saying you get an error telling you to read MySQL documentation in SQL Server? — James Z
– James Z, Commented May 17, 2016 at 15:24

Lukasz Szozda · Accepted Answer · 2016-05-17 15:24:14Z

1

If MySQL 5.7.6+ Generated Columns:

ALTER TABLE randomquestions ADD Total INT AS (R1_Number + R2_Number + R3_Number + R4_Number) STORED;

Note that you may need to change datatype depending of datatype of Rx_Number.

edited May 17, 2016 at 15:24

answered May 17, 2016 at 15:19

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

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

1 Comment

TheGameiswar Over a year ago

But error message says otherwise ?,please update question

Fabian Arias · Accepted Answer · 2016-05-17 15:36:38Z

Alter looks good missing parenthesis and column type.

ALTER TABLE randomquestions ADD Total int AS (R1_Number + R2_Number + R3_Number + R4_Number) PERSISTED

Brandon Parman · Accepted Answer · 2016-05-17 15:37:09Z

I tried a similar statement with the parenthesis and it worked:

use test alter table [dbo].[tablename] Add total2 as (price * 2) persisted

(so I guess it's the parenthesis, it's not finding your table, or just a syntax error with the field names.)

Collectives™ on Stack Overflow

Adding in SQL Column that Totals Other Column Values

3 Answers 3

1 Comment

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Related