MySQL update with same table values

I have a MySQL table named "users" that has the columns "firstname" and "surname". There are a thousand or so rows in the table.

I have now added another column named "search" and I would like to populate it with the values of both the first name and the surname separated by a space. For example, if firstname = Bob and surname = smith, i would like to populate "search" wit开发者_开发知识库h "Bob Smith".

Can anyone advise on an update statement that selects these values and inserts them in to the new column?

Best regards, Ben.

---

You could simply use...

UPDATE users SET search=TRIM(CONCAT(firstname, ' ', surname));

As an explanation, CONCAT simply concatenates (merges) the supplied fields/values, and TRIM will remove any leading or trailing spaces, hence ensuring that there are no issues if a firstname/lastname or indeed both are missing. See the MySQL String Functions manual page for more information on these functions.

However, I'd be tempted to call the new column "name" (or indeed "full_name") rather than "search", as this is, at best, a somewhat misleading field name.

---

update users
set search = CONCAT(firstname, ' ', surname)

This script will update it.

I'm not sure about MySql, but Sql Server lets you set a field as a calculated column. You can specify the calculation as "ISNULL(FirstName, ' ') + ' ' + ISNULL(Surname, ' ')" and set it as persisted so it doesn't calculate it each time. In that case, you wouldn't need an update script

---

update `users` set `search` = CONCAT(`firstname`, ' ', `surname`);