MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE `tb_legacy` (
  `f_makemodel` varchar(128) DEFAULT NULL,
  `f_makeID` INT DEFAULT NULL,
  `f_modelID` INT DEFAULT NULL
);

CREATE TABLE `tb_makes` (
  `f_makeID` INT DEFAULT NULL,
  `f_makename` varchar(128) DEFAULT NULL
);

CREATE TABLE `tb_models` (
  `f_modelID` INT DEFAULT NULL,
  `f_modelname` varchar(128) DEFAULT NULL
);


INSERT INTO `tb_legacy`
    (f_makemodel)
VALUES
    ('CHEVROLET CAPRICE'),
    ('CHEVROLET 1500 TRUCK'),
    ('FORD MUSTANG');
    
INSERT INTO `tb_makes`
    (f_makeID,f_makename)
VALUES
    (1,'Chevrolet'),
    (2,'Ford');

INSERT INTO `tb_models`
    (f_modelID,f_modelname)
VALUES
    (1,'Caprice'),
    (2,'1500 Truck'),
    (3,'Mustang');
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
select * from tb_legacy


f_makemodel
f_makeID
f_modelID


CHEVROLET CAPRICE
1
1

CHEVROLET 1500 TRUCK
1
2

FORD MUSTANG
2
3