Jump to content

Computed Column Does Not Update And Unable To Insert Row


Recommended Posts

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
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
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.

Link to post
Share on other sites
This thread is more than a year old.

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.