Search code examples
mysqlstringsubstringvarchar

MYSQL: Update a column value from a substring in another column?

I have the following MYSQL table:

Job | Applicant  | link_Id

1    job_blogs_123    ?

2    steve_jobs_445  ?

The link_Id column in my table is currenty null for all rows.

I want it to be the substring after the last _ in the Applicant column. So in this case 123 and 445.

How can I write a query that will update all the rows in my table in this way?

Solution

  • Use SUBSTRING_INDEX:

    UPDATE yourTable
SET link_Id = SUBSTRING_INDEX(Applicant, '_', -1);