jusher jusher - 6 months ago 9
SQL Question

MySQL: Modify column based on column values in same table

I've got two columns in the same table for my users:

name-displayed
and
short-name
.

name-displayed
is populated with the full name of the user, for example "John Doe". In
short-name
, there is the short value, e.g. "john-doe" (essentially de-capitalized and hyphenated).

How would I amend the data in
short-name
based on the data in
name-displayed
? I'm sure I could use a self-join based on
UPDATE
, but I'm not sure how to implement a change in data across the columns.

Any help would be hugely appreciated!

Answer

You need to use the Lower and Replace functions for this. See: Lower and Replace in the docs.

Update <table_name> 
    set `short-name` = REPLACE(LOWER(`name-displayed`), ' ','-') 
    where <conditions>;

In case you want this done automatically, you'll need to write a trigger as Walter_Ritzel suggests.

delimiter //
CREATE TRIGGER auto_set_short_name BEFORE INSERT ON account
FOR EACH ROW
BEGIN
    SET NEW.`short-name` = REPLACE(LOWER(`name-displayed`), ' ','-');

END;//
delimiter ;
Comments