[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
4
Medium Priority
?
325 Views
Last Modified: 2012-08-14
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?
0
Comment
Question by:EddieShipman
  • 2
  • 2
4 Comments
 
LVL 24

Expert Comment

by:johanntagle
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

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

Accepted Solution

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

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

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

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

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question