global update

[Ian Selby]

Hi all, I need to update some data in a table, based on some criteria.
In this case we are talking about the stamping of a price against a job.
The update table holds the jobs, and the update_details table holds the
activities performed on each job and the cost for each activity. If i
pull back this information using the following code

select t1.reference,t1.update_id, t2.*
from update t1, update_details t2
where left(t1.reference,2) in ('EA','ND','SD','ST')
and t1.update_id = t2.update_id

I get something like

EA 1883 Act1 4.20
EA 1883 Act2 3.00
EA 1883 Act3 7.50
EA 2444 Act1 4.20
SD 5433 Act1 5.60

I need to update the cost for everything pulled back using the above
sql, to a price determined in another table (activities)

the activities table would look something like

Activity_Code Cost_London Cost_Roc
Act1 5.60 4.20
Act2 4.00 3.00
Act3 6.20 5.60
in a nutshell i need to update the cost in the update details from
Cost_roc to Cost_london for all activities for all jobs in the update
table that have a referance starting with specific letters. The new
prices need to be obtained from the activities table.

Would be very gratefull for any help on this matter

Regards,

Ian Selby

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Erland Sommarskog]

Ian Selby (ia*******@lojics.co.uk) writes:

Hi all, I need to update some data in a table, based on some criteria.
In this case we are talking about the stamping of a price against a job.
The update table holds the jobs, and the update_details table holds the
activities performed on each job and the cost for each activity. If i
pull back this information using the following code
...

Your question seems to have been left unanswered, and unfortunately I
cannot provide any answer to you. The reason for this, is that I cannot
understand how the values in the Cost_London and Cost_Roc column
maps to the rows in the first result set.

The standard recommendation for getting help with a query is to post:

o CREATE TABLE scripts of the tables involved. (It helps to include
PRIMARY KEY and FOREIGN KEY references.
o INSERT statements with sample data.
o The result you want given the sample data.

This makes it easier to understand what you after, and also it makes it
possible to post a tested solution.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp