Updating field based on fields in other tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Updating field based on fields in other tables

Author

Topic

titch79
Starting Member

1 Post

Posted - 2009-01-19 : 08:23:11

Car
---
CarID nchar(10) PK 1 2 3 4
CarMake nchar(10) 1 1 1 2
CatColour nchar(10) R B R W
Type nchar(10) BX EA BX BX
Qty smallint 1 10 2 3
Value int 100 50 200 225

Cost
----
CostID nchar(10) PK 1 1 2 2
Type nchar(10) PK BX EA BX EA
UnitCost int 100 5 75 7

Car.CarMake = Cost.CostID
Car.Type = Cost.Type

I want to calculate value using the CarMake, Type and Qty. I do not think that it would be possible to calculate value as the record is added so I thought that I would leave it blank and then use a procedure to update the value. I tried to use the calcuated field and this works fine if the fields are in the same table. I can see now why this would work. I have tried to use a procedure but after trying different things I still get 'the multipart identifier could not be bound' or 'UnitCost could not be bound'.

Is it possible to use a procedure to do this or should something else be used? If it is ok to use a procedure any suggestions that I could try?

Thanks for reading this and any help you may be able to offer.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-19 : 08:28:14

See http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-20 : 02:55:25

should be a simple join i feel like


UPDATE c
SET c.Value=c.Qty*ct.UnitCost
FROM car c
JOIN Cost ct
ON ct.CostId=c.CarMake
AND ct.Type=c.Type

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources