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(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(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(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(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(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(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(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(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(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(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(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(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(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(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(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(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(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(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