Question 179050 in Mysql Question posted in
The official MySQL documentation can be found here.

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

I have a table with the following example data:

ID Name lat long
1 100 123 -123
2 101 234 -234
4 100-L NULL NULL
5 100-R NULL NULL
6 101-L NULL NULL
7 101-R 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, the desired output would be:

ID Name lat long
1 XX100 123 -123
2 YY101 234 -234
4 XX100-L 123 -123
5 XX100-R 123 -123
6 YY101-L 234 -234
7 YY101-R 234 -234

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

Tags:

2

Answers


  1. 0

    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) = t2.Name 
SET t1.lat = t2.lat,
    t1.long = t2.long
WHERE t1.lat IS NULL;

    Output:

    ID Name lat long
    1 100 123 -123
    2 101 234 -234
    4 100-L 123 -123
    5 100-R 123 -123
    6 101-L 234 -234
    7 YY101-R 234 -234

    Check the demo here.

    Login or Signup to reply.
  2. 0

    you can do it by left joining with a list of names that have lat or long not null :

    select t.ID, t.Name, s.lat, s.`long`
from mytable t
left join (
  select Name, lat, `long`
  from mytable
  where lat is not null or `long` is not null
) as s on t.Name like concat(s.Name, '%');

    The update statement can be :

    update mytable t
left join (
  select Name, lat, `long`
  from mytable
  where lat is not null or `long` is not null
) as s on t.Name like concat(s.Name, '%')
set t.lat = s.lat,
    t.`long` = s.`long`

    Demo here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search