SQL noob question

[professor1942]

I have a table in a database which has one field I need to update for all instances. My problem is the value of the field will be derived from other another field plus fields in two other tables, eg.
-----------------------------------------------------------
Table A contains:

Table B + Table C PK's (combined as PK for Table A)
quantity (int)
total_price (money) - this is the one that needs to be updated

Table B contains:
unit_price (money)

Table C contains:
shipping_charge (money)

-----------------------------------------------------------

total_price will be (quantity from Table A * unit_price from Table B) + shipping_charge from Table C. No problem for a SELECT statement, but how in hell do I UPDATE each total_price field with the calculations?

TIA


BTW I am using MS SQL Server 2005.

 

[jbubrisk]

I don't know MS SQL, but I know MYSQL. I think you would just do a select of each option and then use that for the update?
UPDATE TableA SET TableA_Value = ((SELECT quantity FROM TableA...) * (SELECT price FROM TableB) + (SELECT shipping_cost FROM TableC)) WHERE PK = ...

 

[DT4K]

You can join the tables just like you would in a select statement, or you can use a subquery.

Update tableA
set total_price = tableA.quantity * tableB.Unit_price + tableC.Shipping_charge
from tableA, TableB, TableC
where tableA.whateverykey = tableB.whateverkey
and tableA.whateverKey = tableC.whateverkey

I'm not sure if you can use the tableA join tableB on tableA.key = tableB.Key syntax or not. For some reason, the sql examples I see use the oracle style join syntax.

 

[Markbnj]

I can't test this here, so don't quote me...

UPDATE TableB SET TotalPrice =
SELECT ((TableA.Quantity * TableB.UnitPrice) + TableC.Shipping))
WHERE TableA.ProdID = TableB.ProdID AND TableC.ProdID = TableB.ProdID

You probably need another AND in there to grab the right row in TableB, which is probably something like order id? If not, then I think something is wrong with your scheme and you might want to post it so we can look at it.

Like I said, can't test this here, but something like that should work since you can perform general arithmetic on column values in a SELECT.

But I would really write a stored procedure for this.

[Yep, as the previous two posters said while I was typing 🙂]

 

[professor1942]

Thanks a lot guys, I got it working (it turns out I just had a closing parenthesis in the wrong place :/ )