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.