Help needed for Update Query

[britania]

Im looking for some help by a MSSQL 2000 expert.

I need to update dbo.prop_main with information from another table dbo.properties

Basically where prop_propertyID = Prop_ID in both tables

add the northings_COORD and eastings_COORD information from dbo.properties to the northings and eastings from dbo.prop_main

Can anyone help me construct this?

dbo.prop_main
prop_propertyID
northings
eastings


dbo.properties
Prop_ID
northings_COORD
eastings_COORD

[deepuv04]

Im looking for some help by a MSSQL 2000 expert.

I need to update dbo.prop_main with information from another table dbo.properties

Basically where prop_propertyID = Prop_ID in both tables

add the northings_COORD and eastings_COORD information from dbo.properties to the northings and eastings from dbo.prop_main

Can anyone help me construct this?

dbo.prop_main
prop_propertyID
northings
eastings


dbo.properties
Prop_ID
northings_COORD
eastings_COORD

Is this the query you are looking for...

UPDATE Prop_Main
SET northings = northings + properties.northings_COORD,
    eastings = eastings + properties.eastings_COORD
FROM Prop_Main INNER JOIN
     properties ON properties.Prop_ID = Prop_Main.prop_propertyID
 

[britania]

Thanks for the reply :) it looks promising.

Will that query add the northings_COORD and eastings_COORD from the Properties table to the Prop_Main northings and eastings table columns?

The query resolves on Query Analyzer, so good so far, I have to work with live data on this one, so double checking.

so basically anywhere the ids = in both tables add the coords from properties to prop_main

[deepuv04]

Thanks for the reply :) it looks promising.

Will that query add the northings_COORD and eastings_COORD from the Properties table to the Prop_Main northings and eastings table columns?

The query resolves on Query Analyzer, so good so far, I have to work with live data on this one, so double checking.

so basically anywhere the ids = in both tables add the coords from properties to prop_main

hi,
That will work.. try using some test data or
First select the live data for testing using select statement and if you feel comfortable with that you can implement on live data

thanks

[britania]

many thanks - i am going to set a test environment up...i'll let you know if im smiling!

thanks again

[britania]

Ive created a test site.

when i run the query i get this error?:

Server: Msg 446, Level 16, State 9, Line 1
Cannot resolve collation conflict for equal to operation.

[britania]

ive solved it, it needed COLLATE database_default adding to the end off the query as it seems to get in a muddle which database to look at:

SHGproperties ON SHGProperties.Prop_ID = Prop_Main.prop_propertyID COLLATE database_default