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.

 

[Pat Hartman]

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

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.

Therefore, If you want to assume that any record which has a non-zero, non-null value in selling price has the correct value, then you should only update the records with no value for selling price.

And finally, you need to fix the problem going forward by adding the proper validation logic in the form's BeforeUpdate event. If selling price is required, set it to required on the table AND make the default null rather than 0. That way, Access will never allow a record to be saved without a selling price. In your Form's BeforeUpdate event, you could also offer the user the option of accepting the default +30% which you calculate for him if he leaves the field empty. That way you don't force him to do the calculation if that is the value he wants.

 

[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!

 

[Pat Hartman]

I have a client they entered records on a Products Table. Every other necessary information has been entered only selling_Price is left out.

He didn't say selling price was NEVER entered and people get sloppy with their descriptions and it is quite possible, he didn't examine every record. So, as a "safety play", I would not simply update ALL records. I would add the selling price for only records with an empty value. The point is that this is a fix up issue so an update query to "copy" data to store a calculated value makes sense. HOWEVER, the actual problem should be fixed by preventing this omission in the future.

If the selling price NEVER deviates from the +30% - which I seriously doubt - then I agree, both should not be stored. Selling price should always be calculated.

 

[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.

 

[Pat Hartman]

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.

The actual selling price of an item would be stored on the order details record, not in the product table. Maybe calling it the SuggestedSellingPrice would make you feel better. Then on the order details, it could be called ActualSellingPrice to avoid all confusion.

 

[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.

 

[Pat Hartman]

I don't see need to save SellingPrice to Products table. It can be calculated when needed.

ASSUMING the price is ALWAYS + 30%. That isn't normally the case (at least in the several order entry applications that I have built) and I pointed out that if it is ALWAYS + 30% then it should not be stored. It should be calculated as needed.

Order details contains the ACTUAL selling price which may be different from the SuggestedSellingPrice. Have you never had an item discounted at the register because it was missing a button or something minor?

Business rules are dictated by the business, not by us.

 

[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.