By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,363 Members | 2,358 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,363 IT Pros & Developers. It's quick & easy.

Help needed for Update Query

P: 5
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
Mar 27 '08 #1
Share this Question
Share on Google+
6 Replies


deepuv04
Expert 100+
P: 227
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...

Expand|Select|Wrap|Line Numbers
  1. UPDATE Prop_Main
  2. SET northings = northings + properties.northings_COORD,
  3.     eastings = eastings + properties.eastings_COORD
  4. FROM Prop_Main INNER JOIN
  5.      properties ON properties.Prop_ID = Prop_Main.prop_propertyID
  6.  
Mar 27 '08 #2

P: 5
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
Mar 27 '08 #3

deepuv04
Expert 100+
P: 227
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
Mar 27 '08 #4

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

thanks again
Mar 27 '08 #5

P: 5
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.
Mar 28 '08 #6

P: 5
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
Mar 28 '08 #7

Post your reply

Sign in to post your reply or Sign up for a free account.