CODEWITHSUNDEEP
mysqlsqljoinsql-updateconcatenation
Beems
Beems

Reputation: 810

MySQL - Update Parent Table Based on Concatenated JOIN

I have a table of legacy makes/models that are concatenated together, but I've built two new tables with the distinct makes and models that I would like to use to update the table of legacy makes/models to allow for easier updates. The tables look like this:

===============================================   
| tb_legacy                                   |   
===============================================   
| f_makemodel          | f_makeID | f_modelID | 
|----------------------|----------|-----------|   
| CHEVROLET CAPRICE    |   NULL   |   NULL    |
| CHEVROLET 1500 TRUCK |   NULL   |   NULL    |   
| FORD MUSTANG         |   NULL   |   NULL    | 
-----------------------------------------------

=============================    =============================
| tb_makes                  |    | tb_models                 |
=============================    =============================
| f_makeID | f_makename     |    | f_modelID | f_modelname   |
-----------------------------    -----------------------------
|    1     | Chevrolet      |    |     1     | Caprice       |
|    2     | Ford           |    |     2     | 1500 Truck    |
-----------------------------    |     3     | Mustang       |
                                 -----------------------------

What I want to do is UPDATE the tb_legacy table with the f_makeID and f_modelID fields so that I end up with the tb_legacy field looking like this:

===============================================   
| tb_legacy                                   |   
===============================================   
| f_makemodel          | f_makeID | f_modelID | 
|----------------------|----------|-----------|   
| CHEVROLET CAPRICE    |    1     |     1     |
| CHEVROLET 1500 TRUCK |    1     |     2     |   
| FORD MUSTANG         |    2     |     3     | 
-----------------------------------------------

I have a SQL Fiddle showing the SELECT query: https://www.db-fiddle.com/f/pSJiMHNJPB6pBLfYTMCHgX/6

How can create an UPDATE query to accomplish the desired result?

EDIT:

The following illustrates what I want to do, but

UPDATE tb_legacy
SET tb_legacy.f_makeID = 
(
    SELECT tb_makes.f_makeID
    FROM tb_legacy, tb_makes, tb_models
    WHERE tb_legacy.f_makemodel = CONCAT(tb_makes.f_makename,' ',tb_models.f_modelname)
),
tb_legacy.f_modelID = 
(
    SELECT tb_models.f_modelID
    FROM tb_legacy, tb_makes, tb_models
    WHERE tb_legacy.f_makemodel = CONCAT(tb_makes.f_makename,' ',tb_models.f_modelname)
)

But the above query returns the following error: Error: ER_UPDATE_TABLE_USED: You can't specify target table 'tb_legacy' for update in FROM clause

Upvotes: 1

Views: 84

Answers (2)

forpas
forpas

Reputation: 164099

You need to join tb_legacy to the other 2 tables like this:

UPDATE tb_legacy lg
INNER JOIN tb_makes mk ON lg.f_makemodel LIKE CONCAT(mk.f_makename, ' ', '%')
INNER JOIN tb_models md ON lg.f_makemodel LIKE CONCAT('%', ' ', md.f_modelname)
SET lg.f_makeID = mk.f_makeID,
    lg.f_modelID = md.f_modelID

See the demo.

Upvotes: 1

Mithu
Mithu

Reputation: 655

Something like this

 update tb_legacy 
 set f_makeID=t.f_makeID, f_modelID=l.f_modelID
 from tb_makes t
 join tb_legacy l
 ON tb_legacy.f_makemodel = CONCAT(t.f_makename,' ',l.f_modelname)

Upvotes: 0

Related Questions