fixing this zip->lat/long table

Example data:
"zip";"latitude";"longitude"
"00501";"40.9223";"-72.6371"
"00601";"18.1801";"-66.7495"
"00801";"18.3223";"-64.9637"
"00901";"18.4654";"-66.1079"
"01001";"42.0706";"-72.6203"
"01101";"42.1707";"-72.6048"
"01201";"42.4538";"-73.254"
"01301";"42.6055";"-72.601"

Open in new window


What I need to do is make a new entry ending  in '00' for each entry ending in '01' with the same lat/long coords.

Using sample code above result would be:
"zip";"latitude";"longitude"
"00500";"40.9223";"-72.6371"
"00501";"40.9223";"-72.6371"
"00600";"18.1801";"-66.7495"
"00601";"18.1801";"-66.7495"
"00800";"18.3223";"-64.9637"
"00801";"18.3223";"-64.9637"
"00900";"18.4654";"-66.1079"
"00901";"18.4654";"-66.1079"
"01000";"42.0706";"-72.6203"
"01001";"42.0706";"-72.6203"
"01100";"42.1707";"-72.6048"
"01101";"42.1707";"-72.6048"
"01200";"42.4538";"-73.254"
"01201";"42.4538";"-73.254"
"01300";"42.6055";"-72.601"
"01301";"42.6055";"-72.601"

Open in new window


Can I do this with a single query?
LVL 26
Eddie ShipmanAll-around developerAsked:
Who is Participating?
 
johanntagleCommented:
Then it should just be:

insert into table (zip,latitude,longitude) select concat(trim(trailing '01' from zip), '00') zip, latitude, longitude from table where zip like '%01';
0
 
johanntagleCommented:
Try:

select a.zip, a.latitude, a.longitude from
(
select zip, latitude, longitude from table
union
select concat(trim(trailing '01' from zip), '00') zip, latitude, longitude from table where zip like '%01'
) a
order by 1
0
 
Eddie ShipmanAll-around developerAuthor Commented:
I want to UPDATE this table by inserting new rows.
0
 
Eddie ShipmanAll-around developerAuthor Commented:
Had to deal with some duplicates but it worked, thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.