Skip to main content

How to update values in a column based on the values from other tables

How to update column values in table based on values from two tables:

Some time we need to update values in a table based on the join from other table e.g. for a customer calculate and update data values for Order values in from Item Price and Order table.

below is an idea for doing this...

Create Main table to be updated:
SET
 ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER ON
GO
CREATE
TABLE [dbo].[DimCustOrderValue](
[CustID] [int]
NULL,
[CustomerName] [nchar]
(20) NULL,
[OrderValue] [numeric]
(18, 2) NULL
)
ON [PRIMARY]
GO
--Create other required tables:
SET
 ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER ON
GO
CREATE
TABLE [dbo].[DimItemOrder](
[OrderId] [int]
NULL,
[ItemId] [int]
NULL,
[CustID] [int]
NULL
[OrderQuantity] [int]
NULL,
) ON [PRIMARY]
GO
SET
 ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER ON
GO
CREATE
TABLE [dbo].[DimItemPrice](
[ItemID] [int]
NULL,
[ItemName] [nchar]
(20) NULL,
[ItemPrice] [numeric]
(18, 2) NULL
)
ON [PRIMARY]
GO


--Insert data Values into these tables and keep values null for the OrderValue column

--Update Query:
Update
 Main --table to be updated
set
 Main.OrderValue= Sub.Value --Main.OrderValues is the column to be updated
--Select *
From
 dbo.DimCustOrderValue as Main --table to be updated
INNER
 JOIN
(
Select
 a.CustID,
Sum(isnull(a.OrderQuantity,0)* isnull(b.ItemPrice,0)) as Value
from
 dbo.DimItemOrder as a Inner Join 
dbo.DimItemPrice as b
On
a.ItemID=b.ItemID
Group
 by a.CustID
)
 as Sub -- this block is returning calculated value for Order for customers
on
 Main.Custid=Sub.CusTID
Labels:

Comments

Post a Comment

Popular posts from this blog

SSRS Reports - Space/Gap issue between the objects in SSRS Reports

In Reports where some objects are set to be shown/hide based on some expression/filter condition etc. There Could be some white space issue between the objects on the report( i.e. when few objects are hidden in the report, the white space between the objects will accumulate and displayed as a big gap between the objects on the report). Following are few ways using which we can handle this kind of issue: 1. Remove the Gap between the objects in the report layout. 2. Or put a text box and make the border the same as the background color and apply the same show/hide action on it. 3. If you are using a table/matrix then instead of putting the space between objects, just add one static row above the column header for the table/matrix and make the border color the same background color. below is one approach: The report before any space handling: -----------------------------------------------------------------------------  Report Preview when all the o...
6 comments
Read more

How to do Error Handling for Transaction using Try Catch Block

Issue : Some time we get requirements where we have to use transaction to do some DML operations to make sure the unit of work should be done completely. But there could be error while doing this. So, how we can cope up with errors to achieving this? Solution: We can use Transaction in the Try Catch block to make sure that the transaction either successful or rollback if there is any error. Below is an example for this: BEGIN TRY --Start the Try Block.. BEGIN TRANSACTION -- Start the transaction.. UPDATE [dbo] . [Table] SET salary = salary * 1.1 WHERE MID = 6 --DML operations to be done in the transaction COMMIT TRAN -- Transaction Success! END TRY --End of the Try Block BEGIN CATCH --Start the Catch Block IF @@TRANCOUNT > 0 --check the error count for the errors ROLLBACK TRAN --RollBack in case of Error --to raise the error message when there is an issue. Declare the variables and se the values for them DECLARE @Er...
Post a Comment
Read more

SSRS Reports - When few objects are hidden in the report, the white space between the objects will accumulate and displayed as a big gap between the objects on the report

In Reports where some objects are set to be shown/hide based on some expression/filter condition etc. There Could be some white space issue between the objects on the report( i.e. when few objects are hidden in the report, the white space between the objects will accumulate and displayed as a big gap between the objects on the report). Following are few ways using which we can handle this kind of issue: 1. Remove the Gap between the objects in the report layout. 2. Or put a text box and make the border the same as the background color and apply the same show/hide action on it. 3. If you are using a table/matrix then instead of putting the space between objects, just add one static row above the column header for the table/matrix and make the border color the same background color. below is one approach: The report before any space handling: -----------------------------------------------------------------------------  Report Preview when all the obj...
Post a Comment
Read more