2

I have the following issue. I have a table called titles with the following structure:

id int(10),
name varchar(100),

At some point later we added a new column called modified_name. It is defined as the same as name except that it is lower case and has all of the spaces replaced with a -. We added this column and so we needed to now get the right modified name value into each record of that column. To do this we wrote a PHP script that handled that by loading in values from the database and processing them, but that is highly inefficient. Is it possible to write a single UPDATE query that would add the correct value to each record in the titles table. I can think of ways to do this with a stored procedure and a while loop there in, but I want to know if something more efficient is possible. It there any way to achieve something like the following:

UPDATE `titles` 
    SET 
`modified_name` = LOWER(REPLACE(SELECT `name` FROM `titles` WHERE id = PRESENT_VALUE), ' ', '-');

The goal being to SET the modified_title column of every record in the titles table to a unique value that results from that record's name column as followed:

# Before modification update query
name = "Hello Goodbye" 
modified_name = ""    

# After modification update
name = "Hello Goodbye" 
modified_name = "hello-goodbye"

Thank you for your help, any advice on how best to do this would be appreciated.

1 Answer 1

1
UPDATE `titles` SET `modified_name` = LOWER(REPLACE(`name`, ' ', '-'))
Sign up to request clarification or add additional context in comments.

1 Comment

Wow, that worked perfectly. I did not know that type of manipulation was possible. This will definitely help me a great deal in the future. Thank you.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.