mysql syntax for populating a column of an existing table and setting a default value for said column

Question

I have a table and I have added a new column to it. I need to populate this new column and also set the default value for it.

The value of the new col is obtained by concatenating two strings based on the values of other columns: the first string is the sum COL_1 + 10000 the second string is a obtained by stripping everything but the alphanumerics in COL_2

Update TABLE set NEW_COL = CONCAT ((SUM (10000 + COL_1)), (preg_replace('/[\s\W]+/','',COL_2)))

This will be the default value for the column

That line of sql doesn't work and I cannot understand how to properly format it. I would very much appreciate any help with the syntax — florina, Mar 16 '12 at 13:47

score 0 · Answer 1 · edited May 23 '17 at 12:20

0

The reason your update is failing is that preg_replace() is not a valid MySQL function. That's a PHP function. Here's a relevant question that addresses that functionality in MySQL:

How to do a regular expression replace in MySQL?

edited May 23 '17 at 12:20

Community

1
1

answered Mar 16 '12 at 14:28

Ike Walker

64,401
14
110
109

mysql syntax for populating a column of an existing table and setting a default value for said column

1 Answers1