Search code examples
mysqlsql-like

Transferring a value between two tables where one field is %LIKE% another

I'm attempting to transfer between 2 tables, where one column is %LIKE% another.

In the table "juice" I have the fields title, brandid, and id.

In the table "brands" I have id and title.

The title field from juice will always contain an exact match to the title in brands - along with some other strings.

So I'm trying to check whether brands.title is %LIKE% juice.title, and if so put brands.id in the juice.brandid field.

This is what I have currently:

INSERT INTO juice(brandid) 
SELECT id FROM brands 
WHERE title LIKE CONCAT('%', @juice.title, '%')
Solution

  • Sounds more like you want an UPDATE instead of an INSERT. See if

    UPDATE juice
       SET brandid = (SELECT brands.id
                             FROM brands
                             WHERE brands.title LIKE concat('%', juice.title, '%');

    works for you. Note, that this requires, that only one row from brands matches. If more rows match you need to additionally define which one is the right one.