0

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
2
  • Note by doing this complex expression in your JOIN predicate, you risk the MySQL Engine coming up with an inefficient query plan. Depending on how much data is in your tables, your query may perform poorly. If you weren't on such an old version of MySQL you'd be able to add a generated column to your table that persists the results of the expression for you, but that's not possible in MySQL 5.5. Still, you'd be better off adding a column to your bar table where you store the cleaned up version of the ID, and on INSERT manually maintain it with application code, or with a trigger.
    – J.D.
    Nov 26, 2022 at 14:58
  • @J.D. I'd add a column if what I do can't be done in a single query. And I'm not worried about performance since I'd be running the query only once to backfill old data in a table
    – Lynob
    Nov 26, 2022 at 19:42

1 Answer 1

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.

1
  • Thank you so much, both queries seem to run pretty nicely on the db-fiddle. I'll try them on real data on Monday and let you know. Any preference between the queries above? I like that you're concatenating to foo rather than substracting from bar. I think the second query is better because it's not using RLIKE. What do you think?
    – Lynob
    Nov 26, 2022 at 20:45

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.