updating a field based on relationship between 2 mysql tables

Question

Hi

I have 2 mysql tables woith the following:

table_a

id, retailer_message, logos_id

logos

id, name

table_a has a retailer_message field, with records such as 'On sale at Amazon' logos table has records with the name field as 'Amazon' for example.

Now what I want to do, is update the table_a logos_id based on the relationship with the logos table.

So the following example

table_a

id, retailer_message, logos_id
1, On sale at Amazon
2, On sale at Asda

logos

id, name
1, Amazon
2, Misco
3, Asda
4, Tesco

This would then update the table_a and insert the logos_id of 1 and 3 respectively.

Could someone provide an SQL snippet to achieve this?

Thanks

Answer 1

If you always have the same message as the beginning of the message ("On sale at ") you can use something like this:

update table_a set logos_id = (select id from logos where name = substring(retailer_message,12));