Hi, hopefully this question is in the correct place.
I have a SQL Server 2014 database that I am working on for several clients, this is connecting to a Visual Studio (VB) front end application for data entry and validation.
Several of the items in the database are measurements and I felt it was better to use a single unit of measurement throughout the database so have standardised on mm within the database, however some of the users are in the USA and want the ability to view and enter in inches.
If I read a table in full and as is by using the following then everything reads and subsequently updates no problem
"Select * From MaterialTable"
IHowever if I read using this line then the read works correct and the data is displayed in the correct units (have hard coded the 25.4 below, usually is either 1 or 25.4 depending on display units), however when I try to update the table remains unchanged though I do not get any error
"Select material_id, material_description, diameter = case when 1 = 1 then diameter / 25.4 end, wall_thickness = case when 1 = 1 then wall_thickness / 25.4 end From MaterialTable"
I am very much a novice with SQL, is there anything clever I should do to update correctly or am I flogging a dead horse?
If this is not going to work my fallback plan is to read the values into different a different column, hide the original then update the original if the new column is changed and then update, which is a bit more work in the front end but I think should be possible?
Am I missing something simple? Is there a better way?
Thanks in advance for any help.