Copy values from one field to the same field of a different record based on names

Question

I have a table with the following example data:

ID	Name	lat	long
1	XX100	NULL	NULL
2	YY101
3	XX100-TS	123	-123
4	XX100-RS	NULL	NULL
5	XX100-LS
6	YY101-TS	234	-234
7	YY101-RS
8	YY101-LS	NULL	NULL

What I need to do is update the lat and long fields for IDs 4-7 to match the lat/long of the ones without the L/R at the end. So basically XX100-L will have the same lat/long as XX100, YY101-L will have the same as YY100 and so on.

Unfortunately as you can see some of the fields are NULL and some are blank, the desired output of this would be:

ID	Name	lat	long
1	XX100	123	-123
2	YY101	234	-234
3	XX100-TS	123	-123
4	XX100-RS	123	-123
5	XX100-LS	123	-123
6	YY101-TS	234	-234
7	YY101-RS	234	-234
8	YY101-LS	234	-234

Any advice on how to achieve this would be greatly appreciated!

Edit: I have managed to answer the second portion of my question with the following SQL:

UPDATE tab AS t1
JOIN tab AS t2 ON SUBSTRING_INDEX(t1.Name, '-', 1) = SUBSTRING_INDEX(t2.Name, '-', 1)
SET t1.lat = t2.lat,
    t1.`long` = t2.`long`
WHERE t1.Name NOT LIKE '%-TS' AND t2.Name LIKE '%-TS';

Answer 1

Inside your UPDATE statement, you can apply a self join, that matches on the partial common part of the "Name" value, obtainable with SUBSTRING_INDEX.

UPDATE     tab t1
INNER JOIN tab t2
        ON SUBSTRING_INDEX(t1.Name, '-', 1) = SUBSTRING_INDEX(t2.Name, '-', 1)
SET t1.lat = t2.lat,
    t1.long = t2.long
WHERE t1.lat IS NULL AND t2.lat IS NOT NULL;

Output:

ID	Name	lat	long
1	XX100	123	-123
2	YY101	234	-234
3	XX100-TS	123	-123
4	XX100-RS	123	-123
5	XX100-LS	123	-123
6	YY101-TS	234	-234
7	YY101-RS	234	-234
8	YY101-LS	234	-234

Check the demo here.