Jump to content
phdphd

Computed Column Does Not Update And Unable To Insert Row

Recommended Posts

Posted (edited)

Hi All,

I have a table to which I added 2 computed columns like this :

ALTER TABLE foo add number2 DECIMAL GENERATED ALWAYS AS (number*2) STORED, add nombre3 DECIMAL GENERATED ALWAYS AS (number*3) STORED

Two issues :

1. Updating a value in "number" column does not updates "number2" and "number3" columns

2. Inserting a new row generates the error "the value specified for generated column is not allowed".

Thanks!

 

 

Edited by phdphd

Share this post


Link to post
Share on other sites

I have just run this sequence of SQL and no issues

CREATE TABLE `foo` (
  `foo_id` int(11) NOT NULL AUTO_INCREMENT,
  `number` int(11) DEFAULT NULL,
  PRIMARY KEY (`foo_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

INSERT INTO foo (number) VALUES (5),(10);

SELECT * FROM test.foo;
+--------+--------+
| foo_id | number |
+--------+--------+
|      1 |      5 |
|      2 |     10 |
+--------+--------+

--
--  add generated columns
--

ALTER TABLE foo add number2 DECIMAL GENERATED ALWAYS AS (number*2) STORED, add nombre3 DECIMAL GENERATED ALWAYS AS (number*3) STORED;

--
-- check new table
--
SELECT * FROM test.foo;
+--------+--------+---------+---------+
| foo_id | number | number2 | nombre3 |
+--------+--------+---------+---------+
|      1 |      5 |      10 |      15 |
|      2 |     10 |      20 |      30 |
+--------+--------+---------+---------+

--
--  insert a couple more records
--

INSERT INTO foo (number) VALUES (15),(20);

--
--  check again
--

SELECT * FROM test.foo;
+--------+--------+---------+---------+
| foo_id | number | number2 | nombre3 |
+--------+--------+---------+---------+
|      1 |      5 |      10 |      15 |
|      2 |     10 |      20 |      30 |
|      3 |     15 |      30 |      45 |
|      4 |     20 |      40 |      60 |
+--------+--------+---------+---------+

Perhaps you need to give us more details on exactly what you are doing

  • Thanks 1

Share this post


Link to post
Share on other sites

You are right. My mistake. I am using phpMyAdmin. I should have made the tests through SQL statements instead of inserting a row via the Insert tab or editing a value via the View tab.

Share this post


Link to post
Share on other sites

I have added a couple more records by manually editing the table (MySql Workbech) and, again, no issues. (Note - generated values only appear when the output is refreshed)

image.png.c53668cd64a62fc3aaf89cfe06018a0f.png

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.