update statement in a SP

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have the following tables

Table x Table Y
ID Amount ID cost
01 123 01 125
02 240 02 240

I want to add up the value cost from the table Y to the value amount in
the table x that has the same ID, so it becomes


Table x Table Y
ID Amount ID cost
01 248 01 125
02 262 02 22



what will be the the Sql Statement use to make it work in adp.

Thanks

FRED
 
First, I don't understand the result you give for ID = 2. I suppose that
you have made an error while writing your example.

Second, do you want to update the table X directly or simply to have the
result returned in a query?

Third, you question is more related to a SQL-Server programming problem than
it is to DAP. You should ask your question to m.p.sqlserver.programming to
have a better chance of success.

Finally, you can try something like this:

select (X.Amount + Y.Cost) as Result from X inner join Y on X.ID = Y.ID

However, this example do not take account things like missing values or
multiple values in table Y.

S. L.
 
Try

Update X
SET X.Amount = X.Amount+Y.Cost
FROM X,Y
WHERE X.ID = Y.ID

(I found the following exapmle in SQL2000 online help):

UPDATE titles
SET ytd_sales = titles.ytd_sales + sales.qty
FROM titles, sales
WHERE titles.title_id = sales.title_id

Be aware that this is only correct for the first time. By the second time
you would add all Costs again.

Heiko
 
Back
Top