How in SQL divide with 1000

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/ 

https://www.javatpoint.com/sql-update-with-join