function required for updating table values

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

function required for updating table values

Author

Topic

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-08-04 : 01:36:40

[code]
two tables emrappointmentdetails_bak1 and emrtpbilleditems_bak1
table 1 has columns like appointment_id,bill_number
table 2 has columns like appointment_ids,tp_bill_number
we need to move that is to update the values from tp_bill_number to bill_number using mapping as appointment_id and appointment_ids from both tables.
appointment_is int data type and appointment_ids is varchar

sample data is like this
appointment_id bill_number(emrappoinmtents_bak1 table)
2363 NULL
2404 NULL
2865 NULL
3129 NULL
3538 NULL
3546 NULL
3558 NULL
19214 NULL
19657 NULL
21458 NULL

select * from emrtpbilleditems

tp_id app_id appointment_ids tp_bill_number
803937 805794 NULL 2010000770
803938 805795 NULL 2010000770
803939 805796 NULL 2010000770
803940 NULL 805834,805835,805836 2070000167
803941 805800 NULL 2070000168
803942 NULL 805803,805804,805805,805806 2010000772
803943 805811 NULL 2070000169
803944 805812 NULL 2070000169
803945 805813 NULL 2070000169
803946 804302 NULL 2070000170
803947 805819 NULL 2070000171
803948 805817 NULL 2010000773
803949 805802 NULL 2010000774
803950 805797 NULL 2010000775
803951 805798 NULL 2010000776
803952 805801 NULL 2010000777
803953 805799 NULL 2010000778
803954 NULL 805841,805842,805843,805844,805845,805846,805847,805848,805849,805850,805851 2070000172

thing is appointment_ids values are like 805841,805842,805843,805844,805845 that way we need to parse using each value and update like
805841 121
805842 121
805853 121
\\\\\\805891 121

[/code]

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-08-04 : 01:55:27

HI how can i able to do my requirement using this function

CREATE Function SplitString(@text varchar(8000), @delimiter varchar(1) = ',')
-- This function splits a string of CSV values and creates a table variable with the values.
-- Returns the table variable that it creates
RETURNS @Strings TABLE
(
position int IDENTITY PRIMARY KEY,
member_id varchar(8000)
)

AS

BEGIN
Declare @index int
Set @index = -1

WHILE (LEN(@text) > 0)
BEGIN
SET @index = CHARINDEX(@delimiter , @text)
IF (@index = 0) AND (LEN(@text) > 0)
BEGIN
INSERT INTO @Strings VALUES (@text)
BREAK
END

IF (@index > 1)
BEGIN
INSERT INTO @Strings VALUES (LEFT(@text, @index - 1))
SET @text = RIGHT(@text, (LEN(@text) - @index))
END
ELSE
SET @text = RIGHT(@text, (LEN(@text) - @index))

END
RETURN
END
GO

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-08-04 : 03:32:54

see this

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=115544

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-08-04 : 04:32:56

visakk can you help me out n giving my requirement as i am an oracle developer and relatively new to this sql server.can you please go throuh my requirement and please give me the solution for this

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-08-04 : 04:49:53

want it in a generailsed way to update all but not though parameterised way by passing parameters in function

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-08-04 : 05:58:39

CREATE FUNCTION ParseValues
(@String varchar(8000),
@Delimiter char(1)
)
RETURNS @RESULTS TABLE
(ID int identity(1,1),
Val varchar(1000)
)
AS
BEGIN
DECLARE @Value varchar(100)

WHILE @String is not null
BEGIN
SELECT @Value=CASE WHEN CHARINDEX(@Delimiter,@String) >0 THEN LEFT(@String,CHARINDEX(@Delimiter,@String)-1) ELSE @String END,
@String=CASE WHEN CHARINDEX(@Delimiter,@String) >0 THEN SUBSTRING(@String,CHARINDEX(@Delimiter,@String)+1,LEN(@String)) ELSE NULL END
INSERT INTO @RESULTS (Val)
SELECT @Value
END
RETURN
END
GO

update emrappointmentdetailshistory_bak1 set a.bill_number=(select s.tp_bill_number from
(SELECT s.bill_item_id,s.tp_bill_number,f.Val
FROM emrtpbilleditems_bak1 s
CROSS APPLY dbo.ParseValues(s.appointment_ids,',') f))

EXCEPTION:

Msg 102, Level 15, State 1, Line 4
Incorrect syntax near ')'.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-08-04 : 08:11:43

You didn't give the derived table an alias.


update emrappointmentdetailshistory_bak1 set a.bill_number=(select s.tp_bill_number from 
(SELECT s.bill_item_id,s.tp_bill_number,f.Val
FROM emrtpbilleditems_bak1 s
CROSS APPLY dbo.ParseValues(s.appointment_ids,',') f) b )

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-04 : 08:18:15

Duplicate post http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=130601

N 56°04'39.26"
E 12°55'05.63"

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-08-04 : 08:19:36

Cheers Peso.

waste of time.......

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources