Update values in a table with results from a SELECT statement

I have a receipts table that contains a portion of information. I need to perform an update on this table where...

1) Table "A" is the table I need to update.
2) Table "B" contains values that are used in a caculation with values from table "B"

For example, table "A" contains:

receipt_id
receipt_amt
link_relationship_code

Table "B" contains:
link_relationship_code
hours_worked
hourly_rate

What I need to be able to do is update table "A" where

1) A.link_relationship = B.link_relationship

and set

1) A.value1 = a.receipt_amt  * B.hours_worked
2) A.value2 = a.receipt_amt  / B.hourly_rate

Any help would be appreciated.

Direct SQL or stored procedure are good. This is a SQL Server 2005 release.

bryan_zAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
you mean:
UPDATE a
   SET value1 = a.receipt_amt * b.hours_worked
     , value2 = a.receipt_amt / b.hourly_rate
  FROM table_A a
  JOIN table_B b
    ON A.link_relationship = B.link_relationship

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bryan_zAuthor Commented:
Well that was nifty cool and simple! Thanks! Worked Perfectly.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.