Updating existing fields with data from another field in the same row and data from another table - SQL Server 2019

I have a table TABLE1 that goes like this (just a few records):

I need to split the codigo3 field from TABLE1 so one part will replace codigo1 value and another part will replace codigo4 value, as in this example:

LineaC_Retiro_Turn11 will be split into LineaC and Retiro (the Turn11 part here is of no use) and should modify its own row from this:

to this:

But I have another table, TABLE2, that links the value LineaC (in this example) with a numerical code, 444, and I want also to replace that LineaC with that code, if possible at the same time I am performing the UPDATE. Every value Linea% has its own numeric code, and there are just a few codes. So this example should end up like this:

TABLE1 does not have an ID or a PK. codigo2, which could seem to be one, repeats itself alongside TABLE1 n times; codigo1 is always the same value; and codigo4 is a fare that also repeats itself in TABLE1 with no pattern (it's just a fare), so there is no way to tell which row must be updated unless you take all fields altogether, and I cannot assure that even that would work (mostly because the hour fields hora1 and hora2 don't have the seconds, or they are always '00'), so I have to rely on the row number to make those changes, or alter the table to add a sequence as an ID on a new field.

In either case, with or without an ID field, how do I make the update in order to get TABLE1 codigo1 replaced with the numeric value from TABLE2 and the middle string from TABLE1 codigo3 replacing TABLE1 codigo4?

I'm splitting using the following query:

SELECT Reverse(ParseName(Replace(Reverse(codigo3), '_', '.'), 1)
    ) AS part1,
    Reverse(ParseName(Replace(Reverse(x.codigo3), '_', '.'), 2)
    ) AS part2
FROM TABLE1

But it does not return just one row but all the existing rows in TABLE1, so I need to select just the "codigo3" value corresponding to the same row I want to update.

I used a CTE split to calculate the part1 and new_codigo4 values. The CTE is then joined with TABLE1 and TABLE2 to update the codigo1 and codigo4 fields in TABLE1.

-- Create TABLE1
drop table if exists table1;


CREATE TABLE TABLE1 (
    fecha1 DATE,
    hora1 DATETIME,
    fecha2 DATE,
    hora2 DATETIME,
    codigo1 VARCHAR(50),
    codigo2 BIGINT,
    codigo3 VARCHAR(50),
    codigo4 VARCHAR(50)
);

drop table if exists table2;

-- Create TABLE2
CREATE TABLE TABLE2 (
    linea VARCHAR(10),
    numeric_code INT
);


-- Insert sample data into TABLE1
INSERT INTO TABLE1 (fecha1, hora1, fecha2, hora2, codigo1, codigo2, codigo3, codigo4)
VALUES
('2022-08-12', '1900-01-01 07:00:00.000', '2022-08-13', '1900-01-01 01:18:00.000', '612', '1837520919', 'LineaA_Loria_Turn12', '1500'),
('2022-08-12', '1900-01-01 07:00:00.000', '2022-08-13', '1900-01-01 01:18:00.000', '612', '1812527159', 'LineaD_Callao_Turn9', '750'),
('2022-08-12', '1900-01-01 07:00:00.000', '2022-08-13', '1900-01-01 01:18:00.000', '612', '1838404812', 'LineaE_Virreyes_Turn5', '750'),
('2022-08-12', '1900-01-01 07:00:00.000', '2022-08-13', '1900-01-01 01:18:00.000', '612', '1843766323', 'LineaC_Constitucion_Turn15', '750'),
('2022-08-01', '1900-01-01 19:36:00.000', '2022-08-02', '1900-01-01 01:01:00.000', '612', '1732054697', 'LineaC_Retiro_Turn11', '1500'),
('2022-08-01', '1900-01-01 19:37:00.000', '2022-08-02', '1900-01-01 01:01:00.000', '612', '1835202082', 'LineaH_Hospitales_Turn1', '3000'),
('2022-08-01', '1900-01-01 19:37:00.000', '2022-08-02', '1900-01-01 01:01:00.000', '612', '1828702502', 'LineaC_Independencia_Turn6', '1500');

-- Insert sample data into TABLE2
INSERT INTO TABLE2 (linea, numeric_code)
VALUES
('LineaA', 111),
('LineaB', 222),
('LineaC', 444),
('LineaD', 333),
('LineaE', 555),
('LineaH', 666);



WITH split AS (
    SELECT *,
           LEFT(codigo3, CHARINDEX('_', codigo3) - 1) AS part1,
           SUBSTRING(codigo3, CHARINDEX('_', codigo3) + 1, CHARINDEX('_', STUFF(codigo3, CHARINDEX('_', codigo3), 1, ' ')) - CHARINDEX('_', codigo3) - 1) AS new_codigo4
    FROM TABLE1
)
UPDATE t1
SET t1.codigo1 = t2.numeric_code,
    t1.codigo4 = split.new_codigo4
FROM TABLE1 t1
JOIN split ON t1.codigo2 = split.codigo2
JOIN TABLE2 t2 ON t2.linea = split.part1;


select * from table1

fiddle