CODEWITHSUNDEEP
mysqlsql
Lim
Lim

Reputation: 33

New row value to be sync with entire column

I have a question, how to set a new row to have the same value as the column?

I have a table named pm_sys, it consists of a column called mainv

In mainv, I wish every row in the entire column are in the same value so I had UPDATE pm_sys SET mainv=:mv :mv is the value that inserted by user

But, when I created a new row, the mainv of the new row is not sync with the other values in mainv, the output of the new row of mainv is = 0. Is there any way to solve this problem?

Problem Example Table:
Name    | mainv |
Susan   |   9   |
Savannah|   9   |
Trevor  |   0   | //It is not sync with the rest that already set to 9

Upvotes: 0

Views: 81

Answers (2)

Bilal Siddiqui
Bilal Siddiqui

Reputation: 3629

You can create a trigger on your table that will update mainv column value for newly inserted record based on max value in other records mainv column.

CREATE TRIGGER `default_mainv_value` 
AFTER INSERT ON `pm_sys` 
FOR EACH ROW  
    UPDATE `pm_sys` SET `mainv` = (select max(mainv) from `pm_sys`) WHERE `id` = NEW.id;

Upvotes: 1

danblack
danblack

Reputation: 14761

A generated column can be a constant expression. To change the value use ALTER TABLE changing the row definition.

The reason you'd want a tables returning a constant number seems very odd. If you explain it more there may be a better solution for your needs.

Upvotes: -1

Related Questions