Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1016
  • Last Modified:

Mysql Auto Fill Column with string containing the primary key & another column value or string

Hi,
Lets say I have a table with 2 columns as shown below. I would like to add a third column having a default string value composed of PK and NAME

PK      NAME      
1          Steve
2          Bob

The new column should automatically be filled with
1Steve
2Bob

So the final will be

PK      NAME       New Column Auto Fill
1          Steve    1_Steve

2          Bob         2_Bob
0
ucsdmbdm
Asked:
ucsdmbdm
1 Solution
 
AriMcCommented:
I don't think that is possible but you could always create a view to accomplish the same thing:


create table mytable (pk int, name char(100));
insert into mytable values (1, 'Steve');
insert into mytable values (2, 'Bob');
create view myview as (select pk,name,concat(pk,'_',name) as newcolumn from mytable);
select * from myview;

Open in new window

0
 
ucsdmbdmAuthor Commented:
Hi, How about concatinating a string to PID and storing that as the default value ?

0
 
K VDatabase ConsultantCommented:
creating view is a better option than changing in a database.
You can't assign this as a default value.

The alternative way is creating a trigger on insert / update that will populate /update data in NewColumn.
0
 
crazedsanityCommented:
I would suggest just using a select statement to accomplish it, instead of purposely injecting redundant data:
SELECT PK, NAME, CONCAT(PK, '_', NAME) FROM ...

Open in new window

0
 
earth man2Commented:
Use a view and  create multi column index if speed is what you are looking for.
http://www.postgresql.org/docs/9.1/static/indexes-multicolumn.html
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now