SQL Parsing/Update/Insert Query

[LFDavidson]

New to this site and new to learning SQL so hopefully this is an acceptable post:

I have two tables in MSSQL, one that contains a field with concatenated data that I need to query and put the data into two separate fields in another table.

Table - Contact

I identify the correct records when field "First" has the code GPS. The content of the field "Contact_Point" holds the concatenated GPS Coordinates similar to 35.8516720N,78.5809430W and the number of decimal places will vary.

Table - Customer

I need to separate the GPS Coordinates in the Table Contact/Field Contact_Point and insert them into the correct records in the fields "X-Coordinate" and "Y-Coordinate" in the Customer Table.

"ID" Field in Customer = "Customer_ID" Field in Contact

Looking for help on how to write the query and update to do this.

 

[MichaelF81]

Update Customer
Set    x = Left(Contact_Point, CharIndex(', ', Contact_Point) - 1),
       y = LTrim(Right(Contact_Point, Len(Contact_Point)) - CharIndex(', ', Contact_Point))
GO

"Adults are just obsolete children and the hell with them." - Dr. Seuss

 

[bborissov]

UPDATE Customer SET XCoordinate =
                    CAST(LEFT(Contact.Contact_Point, CHARINDEX(',', Contact.Contact_Point)-1) AS XCoordinate type here),
                    YCoordinate =
                    CAST(SUBSTRING(Contact.Contact_Point, CHARINDEX(',', Contact.Contact_Point)+1,500) AS YCoordinate type here)
      FROM Customer
      INNER JOIN Contact ON Customer.Id = Contact.Customer_ID

not tested, make a good backup first

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[MichaelF81]

Update Customer
Set    x = Left(Contact_Point, CharIndex(',', Contact_Point) - 1),
       y = LTrim(Right(Contact_Point, Len(Contact_Point)) - CharIndex(',', Contact_Point))

oops, two small mistakes, had a mistake after the comma in quotes, and the go is not needed.

If I read it right, the field 'Contact_Point' in the 'Contact' table has the concatanated data, and you want to put the split values (at the comma) into the 'Customer' Table. yes?




"Adults are just obsolete children and the hell with them." - Dr. Seuss

 

[gmmastros]

While you are at it, you should also include a where clause.

[tt][blue]Where CharIndex(',', Contact_Point) > 0[/blue][/tt]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[LFDavidson]

Thanks for the help... MichaelF81, you are correct about the concatenated data is in the field 'Contact_Point' and then need to be split at the comman into the Customer table x and y fields...

I will test this out on a test database...

 

[LFDavidson]

Just noticed. It does need to select only records in Contact where the field 'First' has the code GPS only.. Records without the code GPS in 'First' would not be processed..

 

[LFDavidson]

Would a simple select with where clause as the first line work??

 

[gmmastros]

Probably. Something like this...

Insert Into Customer(Id, XCoord, YCoord)
Select Customer_ID,
       Left(Contact_Point, CharIndex(',', Contact_Point) - 1),
       LTrim(Right(Contact_Point, Len(Contact_Point)) - CharIndex(',', Contact_Point))
From   Contact
Where  [first] = 'GPS'
       And CharIndex(',', Contact_Point) > 0

I also noticed that you have 'direction' indicators on your lat/lon pairs. 35.8516720[!]N[/!],78.5809430[!]W[/!]

If the data type for the XCoord and YCoord column in the customer table is numeric, then you have a little more work to do. It can still be done, but it's probably better left unsaid (because it can be confusing).

Run this...

[tt][blue]
Select Column_Name, Data_Type
From Information_Schema.Columns
Where Table_Name = 'Customer'
[/blue][/tt]

Then, post the results back here.



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[LFDavidson]

Its a large table with a lot of fields but the pertinent info for this is:

x_coordinate datatype varchar
y_coordinate datatype varchar

 

[gmmastros]

Then you shouldn't have any problem inserting the data in to the columns because the directional indicators (N, S, E, and W) won't be a problem.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[LFDavidson]

Yes, the data should be fine. However, in double checking everything I see the concatenated data has the x and y coordinates reversed. Contact_point was entered as "latitude,longitude" whereas the standard convention is x coordinate is longitude and the y coordinate is latitude. Therefore, the left value must go to the new y coordinate field and the right value must go to the new x coordinate field.

 

[gmmastros]

Then just change the order of the fields. Simple really.

Insert Into Customer(Id, [!]YCoord, XCoord[/!])
Select Customer_ID,
       Left(Contact_Point, CharIndex(',', Contact_Point) - 1),
       LTrim(Right(Contact_Point, Len(Contact_Point)) - CharIndex(',', Contact_Point))
From   Contact
Where  [first] = 'GPS'
       And CharIndex(',', Contact_Point) > 0

Notice that the XCoord was swapped with the YCoord. The data will be inserted in to the table with whatever column order you specify.

So...

[tt][blue]
Insert Into Table([red]Field1[/red], [green]Field2[/green], Field3)
Select [red]Col1[/red], [green]Col2[/green], Col3
From OtherTable
[/blue][/tt]

In this example, Col1 will be inserted into the Field1 column because field1 is the first field specified in the list of fields AND col1 is the first column selected in the 'select' part of the statement.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[LFDavidson]

Ran it but got the following error:

Syntax error converting the varchar value '34.682938N,81.98757W' to a column of data type int

 

[gmmastros]

Can you show us your actual query. Also, showing some sample data and expected results would also help.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[LFDavidson]

Basically, just ran it as you described above...

Insert Into Customer(Id, y_coordinate, x_coordinate)
Select Customer_ID,
Left(Contact_Point, CharIndex(',', Contact_Point) - 1),
LTrim(Right(Contact_Point, Len(Contact_Point)) - CharIndex(',', Contact_Point))
From Contact
Where [first] = 'GPS'
And CharIndex(',', Contact_Point) > 0

Sample data from fields first and contact_point:

GPS 35.8516720N,78.5809430W

 

[LFDavidson]

Is a join not required?? INNER JOIN Contact ON Customer.Id = Contact.Customer_ID

 

[gmmastros]

There is a parenthesis out of place. Try this...

Insert Into Customer(Id, y_coordinate, x_coordinate)
Select Customer_ID,
       Left(Contact_Point, CharIndex(',', Contact_Point) - 1),
       LTrim(Right(Contact_Point, Len(Contact_Point)) - CharIndex(',', Contact_Point))
From   Contact
Where  [first] = 'GPS'
       And CharIndex(',', Contact_Point) > 0

When posting code, feel free to use TGML markup.

[ignore]

Put your code here.

[/ignore]

It will be easier to read that way.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

>> Is a join not required?? INNER JOIN Contact ON Customer.Id = Contact.Customer_ID

No. It's not required. The query, as written will insert a record in to the customer table for each record in the contact table. If this isn't what you want, then you will need to modify the query.

From your original post....

I need to separate the GPS Coordinates in the Table Contact/Field Contact_Point and [!]insert[/!] them into the correct records in the fields "X-Coordinate" and "Y-Coordinate" in the Customer Table.

If you need to update the record in the customer table to reflect the coordinates that are stored in the contact table, then you will need to use a different query.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[LFDavidson]

One of those days... Yes, I need to update records that exist in the Customer table with the cooresponding record in the Contact table.... Sorry about that...