asked on

update table based on common values.

Update table with common values in two tables:

SELECT ENERDEQ.[Lease Name]+' '+ENERDEQ.[Well Num] AS WELLNAME,
ENERDEQ.[Spud Date],
ENERDEQ.API, ENERDEQ.[Operator Name],
ENERDEQ.Latitude,
ENERDEQ.[BH Longitude],
ENERDEQ.[Lease Name]

FROM

ENERDEQ INNER JOIN ariesadmin_AC_PROPERTY ON (ENERDEQ.[Operator Name] = ariesadmin_AC_PROPERTY.OPERATOR) AND
(ENERDEQ.Longitude = ariesadmin_AC_PROPERTY.LONGITUDE) AND
(ENERDEQ.Latitude = ariesadmin_AC_PROPERTY.LATITUDE) AND
(ENERDEQ.[Well Num] = ariesadmin_AC_PROPERTY.WELL_ID) AND
(ENERDEQ.[Lease Name] = ariesadmin_AC_PROPERTY.LEASE)

WHERE

ENERDEQ.[Lease Name] = AC_PROPERTY.[LEASE] AND
ENERDEQ.[WELL NUM]=AC_PROPERTY.[WELL ID] AND
ENERDEQ.[LATITUDE]=AC_PROPERTY.[LATITUDE] AND
ENERDEQ.[LONGITUDE]=AC_PROPERTY.[LONGITUDE] AND
ENERDEQ.[OPERATOR NAME]= AC_PROPERTY.[OPERATOR];

This query has error, I would appreciate suggestion.

Thank you,

John Tsioumpris

Hello...at first Look it seems like you mispelled the table In the WHERE Or In the JOIN:

SELECT ENERDEQ.[Lease Name] + '  ' + ENERDEQ.[Well Num] AS WELLNAME
	,ENERDEQ.[Spud Date]
	,ENERDEQ.API
	,ENERDEQ.[Operator Name]
	,ENERDEQ.Latitude
	,ENERDEQ.[BH Longitude]
	,ENERDEQ.[Lease Name]
FROM ENERDEQ
INNER JOIN ariesadmin_AC_PROPERTY ON (ENERDEQ.[Operator Name] = ariesadmin_AC_PROPERTY.OPERATOR)
	AND (ENERDEQ.Longitude = ariesadmin_AC_PROPERTY.LONGITUDE)
	AND (ENERDEQ.Latitude = ariesadmin_AC_PROPERTY.LATITUDE)
	AND (ENERDEQ.[Well Num] = ariesadmin_AC_PROPERTY.WELL_ID)
	AND (ENERDEQ.[Lease Name] = ariesadmin_AC_PROPERTY.LEASE)
WHERE ENERDEQ.[Lease Name] = AC_PROPERTY.[LEASE]
	AND ENERDEQ.[WELL NUM] = AC_PROPERTY.[WELL ID]
	AND ENERDEQ.[LATITUDE] = AC_PROPERTY.[LATITUDE]
	AND ENERDEQ.[LONGITUDE] = AC_PROPERTY.[LONGITUDE]
	AND ENERDEQ.[OPERATOR NAME] = AC_PROPERTY.[OPERATOR];

Open in new window

The table is ariesadmin_AC_PROPERTY or AC_PROPERTY ??

Aiysha

ASKER

ariesadmin_AC_PROPERTY.LATITUDE      ariesadmin_AC_PROPERTY.LONGITUDE
47.86867      -104.25796
48.03971      -104.11407

ENERDEQ.LATITUDE      ENERDEQ.LONGITUDE
47.868668355      -104.257962045
48.03970664      -104.11407454

There is another issue with lat and long, the number of digits after decimal are not same. Is there a way to test only limited numbers after decimal?

Thank you,

John Tsioumpris

You could round them to a number of Decimals
Round(YourValune ,n ) where n is the digits after decimal

ASKER CERTIFIED SOLUTION

John Tsioumpris

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Aiysha

ASKER

Thank you.

John Tsioumpris

Glad i could help