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):

fecha1	hora1	fecha2	hora2	codigo1	codigo2	codigo3	codigo4
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

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:

fecha1	hora1	fecha2	hora2	codigo1	codigo2	codigo3	codigo4
2022-08-01	1900-01-01 19:37:00.000	2022-08-02	1900-01-01 01:01:00.000	612	1828702502	LineaC_Retiro_Turn11	1500

to this:

fecha1	hora1	fecha2	hora2	codigo1	codigo2	codigo3	codigo4
2022-08-01	1900-01-01 19:37:00.000	2022-08-02	1900-01-01 01:01:00.000	LineaC	1828702502	LineaC_Retiro_Turn11	Retiro

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:

fecha1	hora1	fecha2	hora2	codigo1	codigo2	codigo3	codigo4
2022-08-01	1900-01-01 19:37:00.000	2022-08-02	1900-01-01 01:01:00.000	444	1828702502	LineaC_Retiro_Turn11	Retiro

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.

Solution

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

fecha1	hora1	fecha2	hora2	codigo1	codigo2	codigo3	codigo4
2022-08-12	1900-01-01 07:00:00.000	2022-08-13	1900-01-01 01:18:00.000	111	1837520919	LineaA_Loria_Turn12	Loria
2022-08-12	1900-01-01 07:00:00.000	2022-08-13	1900-01-01 01:18:00.000	333	1812527159	LineaD_Callao_Turn9	Callao
2022-08-12	1900-01-01 07:00:00.000	2022-08-13	1900-01-01 01:18:00.000	555	1838404812	LineaE_Virreyes_Turn5	Virreyes
2022-08-12	1900-01-01 07:00:00.000	2022-08-13	1900-01-01 01:18:00.000	444	1843766323	LineaC_Constitucion_Turn15	Constitucion
2022-08-01	1900-01-01 19:36:00.000	2022-08-02	1900-01-01 01:01:00.000	444	1732054697	LineaC_Retiro_Turn11	Retiro
2022-08-01	1900-01-01 19:37:00.000	2022-08-02	1900-01-01 01:01:00.000	666	1835202082	LineaH_Hospitales_Turn1	Hospitales
2022-08-01	1900-01-01 19:37:00.000	2022-08-02	1900-01-01 01:01:00.000	444	1828702502	LineaC_Independencia_Turn6	Independencia

fiddle