Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Update values in a table with results from a SELECT statement

Posted on 2009-07-03
2
Medium Priority
?
179 Views
Last Modified: 2012-05-07
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.

0
Comment
Question by:bryan_z
2 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 24774279
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
 

Author Closing Comment

by:bryan_z
ID: 31599666
Well that was nifty cool and simple! Thanks! Worked Perfectly.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
Screencast - Getting to Know the Pipeline

782 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question