SQL Server - How To update any table based on row by row condition in sql server ?

Asked By Dilip Sharma on 26-Jun-12 06:21 AM
hi
i have one table have columns

id
name 
date
periodid


and i have one User define function  name GetPeriodID()
which will take date as input and return  Periodid...

So how can i update my period id .. row by row .. by passing date row by row...
Without using cursor...

Thanks 
Chintan Vaghela replied to Dilip Sharma on 26-Jun-12 06:37 AM

Hi Frndz,

 

Functionality:  Update Row based on function Return value

 

 

To achieve this task,

 

Assign period value to Function return value

 

SET PeriodID = dbo.GetPeriodID(date) 

 

 

Full Logic     :

 

 

 

Update TableName SET PeriodID = dbo.GetPeriodID(date) 

 

    

Hope this helpful!

Thanks

 

 

 

Dilip Sharma replied to Chintan Vaghela on 26-Jun-12 06:40 AM
bat this date will be different according to every row ...
how can i pass each row date one by one and get period id...

one way using cursor .. but  i do not want to use ....

Chintan Vaghela replied to Dilip Sharma on 26-Jun-12 06:42 AM
can you send me your data
Dilip Sharma replied to Chintan Vaghela on 26-Jun-12 06:48 AM
CREATE TABLE [dbo].[MyTest](
  [EntryDate] [datetime] NULL,
  [Source] [varchar](50) NULL,
  [yearid] [bigint] NULL,
  [periodid] [bigint] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[MyTest] ([EntryDate], [Source], [yearid], [periodid]) VALUES (CAST(0x0000A09400000000 AS DateTime), N'FIASS', NULL, NULL)
INSERT [dbo].[MyTest] ([EntryDate], [Source], [yearid], [periodid]) VALUES (CAST(0x0000A09400000000 AS DateTime), N'FIASS', NULL, NULL)
INSERT [dbo].[MyTest] ([EntryDate], [Source], [yearid], [periodid]) VALUES (CAST(0x0000A0B300000000 AS DateTime), N'FIASS', NULL, NULL)
INSERT [dbo].[MyTest] ([EntryDate], [Source], [yearid], [periodid]) VALUES (CAST(0x0000A0B300000000 AS DateTime), N'FIASS', NULL, NULL)
INSERT [dbo].[MyTest] ([EntryDate], [Source], [yearid], [periodid]) VALUES (CAST(0x0000A0D200000000 AS DateTime), N'FIASS', NULL, NULL)
INSERT [dbo].[MyTest] ([EntryDate], [Source], [yearid], [periodid]) VALUES (CAST(0x0000A0D200000000 AS DateTime), N'FIASS', NULL, NULL)
INSERT [dbo].[MyTest] ([EntryDate], [Source], [yearid], [periodid]) VALUES (CAST(0x0000A0F000000000 AS DateTime), N'FIASS', NULL, NULL)
INSERT [dbo].[MyTest] ([EntryDate], [Source], [yearid], [periodid]) VALUES (CAST(0x0000A0F000000000 AS DateTime), N'FIASS', NULL, NULL)
INSERT [dbo].[MyTest] ([EntryDate], [Source], [yearid], [periodid]) VALUES (CAST(0x0000A10F00000000 AS DateTime), N'FIASS', NULL, NULL)
INSERT [dbo].[MyTest] ([EntryDate], [Source], [yearid], [periodid]) VALUES (CAST(0x0000A10F00000000 AS DateTime), N'FIASS', NULL, NULL)
INSERT [dbo].[MyTest] ([EntryDate], [Source], [yearid], [periodid]) VALUES (CAST(0x0000A12D00000000 AS DateTime), N'FIASS', NULL, NULL)
INSERT [dbo].[MyTest] ([EntryDate], [Source], [yearid], [periodid]) VALUES (CAST(0x0000A12D00000000 AS DateTime), N'FIASS', NULL, NULL)
INSERT [dbo].[MyTest] ([EntryDate], [Source], [yearid], [periodid]) VALUES (CAST(0x0000A14C00000000 AS DateTime), N'FIASS', NULL, NULL)
INSERT [dbo].[MyTest] ([EntryDate], [Source], [yearid], [periodid]) VALUES (CAST(0x0000A14C00000000 AS DateTime), N'FIASS', NULL, NULL)
INSERT [dbo].[MyTest] ([EntryDate], [Source], [yearid], [periodid]) VALUES (CAST(0x0000A16B00000000 AS DateTime), N'FIASS', NULL, NULL)
INSERT [dbo].[MyTest] ([EntryDate], [Source], [yearid], [periodid]) VALUES (CAST(0x0000A16B00000000 AS DateTime), N'FIASS', NULL, NULL)
INSERT [dbo].[MyTest] ([EntryDate], [Source], [yearid], [periodid]) VALUES (CAST(0x0000A18700000000 AS DateTime), N'FIASS', NULL, NULL)
INSERT [dbo].[MyTest] ([EntryDate], [Source], [yearid], [periodid]) VALUES (CAST(0x0000A18700000000 AS DateTime), N'FIASS', NULL, NULL)
INSERT [dbo].[MyTest] ([EntryDate], [Source], [yearid], [periodid]) VALUES (CAST(0x0000A1A600000000 AS DateTime), N'FIASS', NULL, NULL)
INSERT [dbo].[MyTest] ([EntryDate], [Source], [yearid], [periodid]) VALUES (CAST(0x0000A1A600000000 AS DateTime), N'FIASS', NULL, NULL)
INSERT [dbo].[MyTest] ([EntryDate], [Source], [yearid], [periodid]) VALUES (CAST(0x0000A1C400000000 AS DateTime), N'FIASS', NULL, NULL)
INSERT [dbo].[MyTest] ([EntryDate], [Source], [yearid], [periodid]) VALUES (CAST(0x0000A1C400000000 AS DateTime), N'FIASS', NULL, NULL)
INSERT [dbo].[MyTest] ([EntryDate], [Source], [yearid], [periodid]) VALUES (CAST(0x0000A1E300000000 AS DateTime), N'FIASS', NULL, NULL)
INSERT [dbo].[MyTest] ([EntryDate], [Source], [yearid], [periodid]) VALUES (CAST(0x0000A1E300000000 AS DateTime), N'FIASS', NULL, NULL)
INSERT [dbo].[MyTest] ([EntryDate], [Source], [yearid], [periodid]) VALUES (CAST(0x0000A20100000000 AS DateTime), N'FIASS', NULL, NULL)
INSERT [dbo].[MyTest] ([EntryDate], [Source], [yearid], [periodid]) VALUES (CAST(0x0000A20100000000 AS DateTime), N'FIASS', NULL, NULL)
INSERT [dbo].[MyTest] ([EntryDate], [Source], [yearid], [periodid]) VALUES (CAST(0x0000A22000000000 AS DateTime), N'FIASS', NULL, NULL)
INSERT [dbo].[MyTest] ([EntryDate], [Source], [yearid], [periodid]) VALUES (CAST(0x0000A22000000000 AS DateTime), N'FIASS', NULL, NULL)
INSERT [dbo].[MyTest] ([EntryDate], [Source], [yearid], [periodid]) VALUES (CAST(0x0000A23F00000000 AS DateTime), N'FIASS', NULL, NULL)
INSERT [dbo].[MyTest] ([EntryDate], [Source], [yearid], [periodid]) VALUES (CAST(0x0000A23F00000000 AS DateTime), N'FIASS', NULL, NULL)
INSERT [dbo].[MyTest] ([EntryDate], [Source], [yearid], [periodid]) VALUES (CAST(0x0000A25D00000000 AS DateTime), N'FIASS', NULL, NULL)
INSERT [dbo].[MyTest] ([EntryDate], [Source], [yearid], [periodid]) VALUES (CAST(0x0000A25D00000000 AS DateTime), N'FIASS', NULL, NULL)
INSERT [dbo].[MyTest] ([EntryDate], [Source], [yearid], [periodid]) VALUES (CAST(0x0000A27C00000000 AS DateTime), N'FIASS', NULL, NULL)
INSERT [dbo].[MyTest] ([EntryDate], [Source], [yearid], [periodid]) VALUES (CAST(0x0000A27C00000000 AS DateTime), N'FIASS', NULL, NULL)
INSERT [dbo].[MyTest] ([EntryDate], [Source], [yearid], [periodid]) VALUES (CAST(0x0000A29A00000000 AS DateTime), N'FIASS', NULL, NULL)
INSERT [dbo].[MyTest] ([EntryDate], [Source], [yearid], [periodid]) VALUES (CAST(0x0000A29A00000000 AS DateTime), N'FIASS', NULL, NULL)
INSERT [dbo].[MyTest] ([EntryDate], [Source], [yearid], [periodid]) VALUES (CAST(0x0000A2B900000000 AS DateTime), N'FIASS', NULL, NULL)
INSERT [dbo].[MyTest] ([EntryDate], [Source], [yearid], [periodid]) VALUES (CAST(0x0000A2B900000000 AS DateTime), N'FIASS', NULL, NULL)
INSERT [dbo].[MyTest] ([EntryDate], [Source], [yearid], [periodid]) VALUES (CAST(0x0000A2D800000000 AS DateTime), N'FIASS', NULL, NULL)
INSERT [dbo].[MyTest] ([EntryDate], [Source], [yearid], [periodid]) VALUES (CAST(0x0000A2D800000000 AS DateTime), N'FIASS', NULL, NULL)
INSERT [dbo].[MyTest] ([EntryDate], [Source], [yearid], [periodid]) VALUES (CAST(0x0000A2F400000000 AS DateTime), N'FIASS', NULL, NULL)
INSERT [dbo].[MyTest] ([EntryDate], [Source], [yearid], [periodid]) VALUES (CAST(0x0000A2F400000000 AS DateTime), N'FIASS', NULL, NULL)
INSERT [dbo].[MyTest] ([EntryDate], [Source], [yearid], [periodid]) VALUES (CAST(0x0000A31300000000 AS DateTime), N'FIASS', NULL, NULL)
INSERT [dbo].[MyTest] ([EntryDate], [Source], [yearid], [periodid]) VALUES (CAST(0x0000A31300000000 AS DateTime), N'FIASS', NULL, NULL)
INSERT [dbo].[MyTest] ([EntryDate], [Source], [yearid], [periodid]) VALUES (CAST(0x0000A33100000000 AS DateTime), N'FIASS', NULL, NULL)
INSERT [dbo].[MyTest] ([EntryDate], [Source], [yearid], [periodid]) VALUES (CAST(0x0000A33100000000 AS DateTime), N'FIASS', NULL, NULL)
INSERT [dbo].[MyTest] ([EntryDate], [Source], [yearid], [periodid]) VALUES (CAST(0x0000A35000000000 AS DateTime), N'FIASS', NULL, NULL)
INSERT [dbo].[MyTest] ([EntryDate], [Source], [yearid], [periodid]) VALUES (CAST(0x0000A35000000000 AS DateTime), N'FIASS', NULL, NULL)
INSERT [dbo].[MyTest] ([EntryDate], [Source], [yearid], [periodid]) VALUES (CAST(0x0000A36E00000000 AS DateTime), N'FIASS', NULL, NULL)
INSERT [dbo].[MyTest] ([EntryDate], [Source], [yearid], [periodid]) VALUES (CAST(0x0000A36E00000000 AS DateTime), N'FIASS', NULL, NULL)
INSERT [dbo].[MyTest] ([EntryDate], [Source], [yearid], [periodid]) VALUES (CAST(0x0000A38D00000000 AS DateTime), N'FIASS', NULL, NULL)
INSERT [dbo].[MyTest] ([EntryDate], [Source], [yearid], [periodid]) VALUES (CAST(0x0000A38D00000000 AS DateTime), N'FIASS', NULL, NULL)
INSERT [dbo].[MyTest] ([EntryDate], [Source], [yearid], [periodid]) VALUES (CAST(0x0000A3AC00000000 AS DateTime), N'FIASS', NULL, NULL)
INSERT [dbo].[MyTest] ([EntryDate], [Source], [yearid], [periodid]) VALUES (CAST(0x0000A3AC00000000 AS DateTime), N'FIASS', NULL, NULL)
INSERT [dbo].[MyTest] ([EntryDate], [Source], [yearid], [periodid]) VALUES (CAST(0x0000A3CA00000000 AS DateTime), N'FIASS', NULL, NULL)
INSERT [dbo].[MyTest] ([EntryDate], [Source], [yearid], [periodid]) VALUES (CAST(0x0000A3CA00000000 AS DateTime), N'FIASS', NULL, NULL)
INSERT [dbo].[MyTest] ([EntryDate], [Source], [yearid], [periodid]) VALUES (CAST(0x0000A3E900000000 AS DateTime), N'FIASS', NULL, NULL)
INSERT [dbo].[MyTest] ([EntryDate], [Source], [yearid], [periodid]) VALUES (CAST(0x0000A3E900000000 AS DateTime), N'FIASS', NULL, NULL)
INSERT [dbo].[MyTest] ([EntryDate], [Source], [yearid], [periodid]) VALUES (CAST(0x0000A40700000000 AS DateTime), N'FIASS', NULL, NULL)
INSERT [dbo].[MyTest] ([EntryDate], [Source], [yearid], [periodid]) VALUES (CAST(0x0000A40700000000 AS DateTime), N'FIASS', NULL, NULL)
INSERT [dbo].[MyTest] ([EntryDate], [Source], [yearid], [periodid]) VALUES (CAST(0x0000A42600000000 AS DateTime), N'FIASS', NULL, NULL)
INSERT [dbo].[MyTest] ([EntryDate], [Source], [yearid], [periodid]) VALUES (CAST(0x0000A42600000000 AS DateTime), N'FIASS', NULL, NULL)
INSERT [dbo].[MyTest] ([EntryDate], [Source], [yearid], [periodid]) VALUES (CAST(0x0000A44500000000 AS DateTime), N'FIASS', NULL, NULL)
INSERT [dbo].[MyTest] ([EntryDate], [Source], [yearid], [periodid]) VALUES (CAST(0x0000A44500000000 AS DateTime), N'FIASS', NULL, NULL)
INSERT [dbo].[MyTest] ([EntryDate], [Source], [yearid], [periodid]) VALUES (CAST(0x0000A46100000000 AS DateTime), N'FIASS', NULL, NULL)
INSERT [dbo].[MyTest] ([EntryDate], [Source], [yearid], [periodid]) VALUES (CAST(0x0000A46100000000 AS DateTime), N'FIASS', NULL, NULL)
INSERT [dbo].[MyTest] ([EntryDate], [Source], [yearid], [periodid]) VALUES (CAST(0x0000A08000000000 AS DateTime), N'PPJ', NULL, NULL)
INSERT [dbo].[MyTest] ([EntryDate], [Source], [yearid], [periodid]) VALUES (CAST(0x0000A08000000000 AS DateTime), N'PPJ', NULL, NULL)
INSERT [dbo].[MyTest] ([EntryDate], [Source], [yearid], [periodid]) VALUES (CAST(0x0000A09F00000000 AS DateTime), N'PPJ', NULL, NULL)
INSERT [dbo].[MyTest] ([EntryDate], [Source], [yearid], [periodid]) VALUES (CAST(0x0000A09F00000000 AS DateTime), N'PPJ', NULL, NULL)
INSERT [dbo].[MyTest] ([EntryDate], [Source], [yearid], [periodid]) VALUES (CAST(0x0000A09F00000000 AS DateTime), N'PPJ', NULL, NULL)
INSERT [dbo].[MyTest] ([EntryDate], [Source], [yearid], [periodid]) VALUES (CAST(0x0000A09F00000000 AS DateTime), N'PPJ', NULL, NULL)
INSERT [dbo].[MyTest] ([EntryDate], [Source], [yearid], [periodid]) VALUES (CAST(0x0000A0BE00000000 AS DateTime), N'PPJ', NULL, NULL)
INSERT [dbo].[MyTest] ([EntryDate], [Source], [yearid], [periodid]) VALUES (CAST(0x0000A0BE00000000 AS DateTime), N'PPJ', NULL, NULL)

Chintan Vaghela replied to Dilip Sharma on 26-Jun-12 06:52 AM
can you send me GetPeriod function detail?
Dilip Sharma replied to Chintan Vaghela on 26-Jun-12 06:56 AM


ALTER FUNCTION [dbo].[Finacial_Period]
(
  @cu_Dt datetime
  
 
)
returns @MyTable table(YearID bigint,PriodID bigint)
 
AS
begin
 
   
   
declare @YID int
declare @PID int

   -- here it will return from join of other table ... -- for testing u can return any value..
   
return
end
Chintan Vaghela replied to Dilip Sharma on 26-Jun-12 07:28 AM

Hi Frndz,

 

Functionality:  Change Function

 

 

To achieve this task,

 

Make function that return string value

 

After then split this string value

 

Check below logic for more detail

 

 

 

Full Logic     :

 

 

Update Query

Update MyTest SET yearid =

 

SUBSTRING(dbo.Finacial_Periods(tbl.EntryDate),0,CHARINDEX('|',dbo.Finacial_Periods(tbl.EntryDate),0)),

 

periodid=

SUBSTRING(dbo.Finacial_Periods(tbl.EntryDate),CHARINDEX('|',dbo.Finacial_Periods(tbl.EntryDate))+1, LEN(dbo.Finacial_Periods(tbl.EntryDate)))

 

from MyTest  as tbl

 

Function

 

ALTER FUNCTION [dbo].[Finacial_Periods]

(

  @cu_Dt datetime

 

 

)

 

RETURNS VARCHAR(MAX)

 

AS

begin

 

  DECLARE @ReturnValue as Varchar(MAX) 

  

declare @YID int

declare @PID int

 

 

SET @YID = 655

SET @PID = 2

-- here it will return from join of other table ...

-- for testing u can return any value..

  

 

return Convert(Varchar(50),@YID) + '|' + Convert(Varchar(50),@PID)

end    

 

 

 

 

 

Hope this helpful!

Thanks