Product:
Microsoft SQL server
Issue:
How update a value in a column in a existing table – for example divide with 1000?
Solution:
UPDATE table_name
SET value1 = value1 / 1000
WHERE condition ;
Keep in mind to check the key dim table, so you include the correct rows in the update.
The key value for the version need to be collected from the version table.
The account value you want to update in fact table may have more than one key value, that you need to check and include.
In the fact table you can use the update command, but you need to carefully test the where clause so you update the correct rows.
Use below code to update the column value where the account is the defined value
UPDATE [DM].[fact] SET value = value / 1000 WHERE 1=1 and key_dimversion = 3 and ( key_dimTabellkonto = 55180 or key_dimTabellkonto = 61453 or key_dimTabellkonto = 62504 or key_dimTabellkonto = 66683 or key_dimTabellkonto = 67730 or key_dimTabellkonto = 69823 )
Use below code to get a sum of column value:
SELECT Sum(value) FROM [DM].[fact] WHERE 1=1 and key_dimversion = 3 and ( key_dimTabellkonto = 55180 or key_dimTabellkonto = 61453 or key_dimTabellkonto = 62504 or key_dimTabellkonto = 66683 or key_dimTabellkonto = 67730 or key_dimTabellkonto = 69823 )
This can be written easier with a SQL JOIN between the tables.
UPDATE a SET a.value= a.value / 1000 FROM [DM].[fact] a INNER JOIN [DM].[dimTabellkonto] b ON a.[key_dimTabellkonto] = b.[key_dimTabellkonto] INNER JOIN [DM].[dimVersion] c ON a.[key_dimVersion] = c.[key_dimVersion] WHERE 1=1 and c.[version] = 'budget' and b.[account] = '95000' ;
To see the sum
SELECT SUM (a.value) FROM [DM].[fact] a INNER JOIN [DM].[dimTabellkonto] b ON a.[key_dimTabellkonto] = b.[key_dimTabellkonto] INNER JOIN [DM].[dimVersion] c ON a.[key_dimVersion] = c.[key_dimVersion] WHERE 1=1 and c.[version] = 'budget' and b.[account] = '95000' ;
More Information:
https://www.w3schools.com/sql/sql_update.asp
https://learnsql.com/blog/sql-division-operator/
https://www.w3schools.com/sql/sql_join.asp
https://www.sqlshack.com/learn-sql-join-multiple-tables/
https://www.geeksforgeeks.org/sql-update-with-join/
https://www.datacamp.com/tutorial/sql-update-with-join
https://www.sqlservertutorial.net/sql-server-basics/sql-server-update-join/
