How to add new column where the value is from query (MySQL)-CodePudding

I have query that split varchar of string and number and only return the number. Is it possible to save result from this Query into a new column on the same table? And I want it to be permanent, meaning if I close and open the database, the result will still be there without I run the query anymore.

Edit :

I want a permanent column that I can modify and interact to. For context, I basically have two Tables, Table1(ColumnA,ColumnB) and Table2(ColumnX,ColumnY).

ColumnA is a varchar and the value is a modified value of ColumnX(PK of Table2), so I want to extract only the integer value form ColumnA, so I can make relation between Table1 and Table2.

Example :

Table1
    ColumnA | ColumnB
    ----------------
    1234ab  | text1
    2345cd  | text2
    456aaa  | text3

Table2
    ColumnX
    1234
    2345
    456

The Query : select REGEXP_SUBSTR(ColumnA,"[0-9] ") as ColumnC from Table1;

Query Result :

ColumnC
---------
1234
2345
456

Expected Result :

Table1
    ColumnA | ColumnB | ColumnC(FK)
    ----------------------------
    1234ab  | text1   | 1234
    2345cd  | text2   | 2345
    456aaa  | text3   | 456

Table2
ColumnX(PK)
1234
2345
456

I've tried to do this UPDATE tablename SET ColumnC = REGEXP_SUBSTR(ColumnA,"[0-9] "); but it return this error :

Unhandled event loop exception
java.lang.StackOverflowError

CodePudding user response：

If you want ColumnC to reflect changes in ColumnA you could use a calculated column:

alter table t 
  add column ColumnC varchar(20) as (REGEXP_SUBSTR(ColumnA,'[0-9] '));

Example fiddle