Update records on a SQL Table

[Engr. Matthew]

I have a client they entered records on a Products Table. Every other necessary information has been entered only selling_Price is left out. Now, they asked me if it is possible to update the entire Products table’s selling_Price with the Value of Purchase_Price plus 30%.

The Selling_Price = Purchase_Price +(Purchase_Price*30%)
Is there SQL Update Syntax to this.

 

[Minty]

On SQL Server

UPDATE TheTableName
SET Selling_Price = Purchase_Price * 1.3

Replace TheTableName with the correct name for the table.
EDIT : Actually re-reading your post I think one of us has the calculation wrong? I assumed you just wanted 30%
Purchase price + (Purchase price *30%) is a much bigger increase?

 

[plog]

Purchase price + (Purchase price *30%) is a much bigger increase?

Equivalent.

100 + (100 * .3) = 100 + 30 = 130
100 * 1.3 =130

 

[theDBguy]

If the selling price can be calculated from the purchase price, it is recommended NOT to store it in the table.

 

[theDBguy]

The selling price may be +30% or it may be some other value which is why it is being stored. This update query is to correct an omission in the original data entry.

I guess I missed that FACT that this about updating only SOME of the records. I had the impression that the OP said that ALL the records need to be updated. I wonder where I could have gotten that idea? Could it be from the original post that said:

Now, they asked me if it is possible to update the entire Products table’s selling_Price with the Value of Purchase_Price plus 30%.

I guess I need more practice reading between the lines. Thanks!

 

[Isaac]

May even be a good idea with something that variable to store datetime-bound segments within which the factor was a certain thing.
Unless the intention is to store the actual selling price as part of a Fact record, in which case maybe it should be stored as a hard value after all.
May depend on the business processes and usage of this attribute.

 

[June7]

I don't see need to save SellingPrice to Products table. It can be calculated when needed.
Only reason to save in OrderDetails is if the percentage can vary over time. In which case, maybe the percentage should be saved, not the calculated result. This is the same justification for saving PurchasePrice into OrderDetails - price could change over time.

 

[June7]

I did suggest saving the discount % was an option.

Would be interesting to know how retail store programs document discounts: member and sale discounts on specific products, mfr coupon, then maybe a discount off the entire purchase. I expect a record for each.

 

[GaP42]

Discounts can be applied to the total sales order or to the individual item in the order.
Further they may be % discounts or a fixed amount.
The discount as applied is stored against the sales record or sales item as appropriate - and linked to a campaign / scheme: Employee discount, managers special, Easter discount (with date limits) etc.
It is possible that some discounts are only available to be applied by certain users (employee role).