How to join on "replace and substract"?

Question

Using MySQL 5.5, no need to upgrade since it's on an old server and only used for Grafana.

I have two tables, foo, and bar. Foo has id and the title is null. I want to get the titles from bar and insert them in foo.

The only problem is that the IDs in bar have the same id in foo but are modified like so "uv10000000 + video_id" and the id in foo has a string length >= 10.

Let's say foo has an id 85, the join should be done on bar on id uv10000085. I should do the following:

• Check if the length of an id in bar >= 10
• If it is, then I should remove the uv
• And then subtract the 10000000
• And only then do the join on foo.

Here's an SQL Fiddle, and here's the query that I would like to do

UPDATE foo as f join bar as b on b.id = f.id
set b.title = f.title

Answer 1

UPDATE bar
JOIN foo ON bar.id RLIKE CONCAT('uv10+', foo.id)
SET bar.title = foo.title;

https://www.db-fiddle.com/f/xrMnwmGkYiBfjyYetE1Pmt/1

---

UPDATE bar
JOIN foo ON bar.id = CONCAT('uv1', LPAD(foo.id, 7, 0))
SET bar.title = foo.title;

https://www.db-fiddle.com/f/xrMnwmGkYiBfjyYetE1Pmt/2

---

Check if the length of an id in bar >= 10

Add this check by yourself, if it is needed.