[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
Solved

# fixing this zip->lat/long table

Posted on 2011-10-10
Medium Priority
325 Views
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"
``````

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"
``````

Can I do this with a single query?
0
Question by:EddieShipman
• 2
• 2

LVL 24

Expert Comment

ID: 36945591
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

LVL 26

Author Comment

ID: 36945611
I want to UPDATE this table by inserting new rows.
0

LVL 24

Accepted Solution

johanntagle earned 2000 total points
ID: 36945625
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

LVL 26

Author Closing Comment

ID: 36949509
Had to deal with some duplicates but it worked, thanks.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
###### Suggested Courses
Course of the Month19 days, 1 hour left to enroll