update required - SQL Server Forums

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)

update required

Author

Topic

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-07-31 : 00:32:41

hi iam two tables emrtpbilleditems and emrappointmentdetails

1)tp_bill_number and appointment_ids are the columns in emrtpbilleditems table.

2)bill_number and appointment_id are the columns available in
emrappointmentdetails table

now i have to update the tp_bill_number values into bill_number column

but appointmnet_ids column values are like 201,202,203 and is having relationship to column appointment_id in emrappointmentdetails table.

we need to fetch and parse based on the appointment_ids values and have to update the bill_bumber column.can you help me out in this

i need output to be

appointment_id bill_number
201 12
202 12
203 12.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-07-31 : 00:39:31

can u post the sample data in the above two table ?

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-07-31 : 00:49:52

select * from emrappointmentdetails

appointment_id patient_id bill_number
8021343 805800 NULL
8021372 805802 NULL
8021377 805793 NULL
8021378 805793 NULL
8021379 805793 NULL
8021381 805792 2050000333
8021382 805792 2050000333
8021383 805792 2050000333
8021384 805792 2050000333
8021385 805792 2050000333

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

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-07-31 : 01:32:54

CREATE TABLE EMRAppointmentDetails (
APPOINTMENT_ID numeric(20, 0) NOT NULL ,
PATIENT_ID VARCHAR(20) NOT NULL ,
BILL_NUMBER VARCHAR(50) NULL
)
GO

CREATE TABLE EMRTPBilledItems (
BILL_ITEM_ID NUMERIC(20, 0) NOT NULL,
APPOINTMENT_ID NUMERIC(20, 0),
APPOINTMENT_IDS VARCHAR(100),
TP_BILL_NUMBER VARCHAR(50)

)
GO

INSERT INTO EMRAppointmentDetails VALUES(2363,'3145',NULL)
GO
INSERT INTO EMRAppointmentDetails VALUES(2404,'3145',NULL)
GO
INSERT INTO EMRAppointmentDetails VALUES(8021493,'805754','2050000315')
GO
INSERT INTO EMRAppointmentDetails VALUES(8021494,'805754','2050000315')
GO

INSERT INTO EMRTPBilledItems VALUES (801501,NULL,NULL,'2030000004')
GO
INSERT INTO EMRTPBilledItems VALUES (803954,NULL,'805841,805842,805843,805844,805845,805846,805847,805848,805849,805850,805851','2070000172')
GO

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-07-31 : 01:58:09

hi i have written fucnction like this and updating will it work as i am not sure about it.

drop function GetAPPids
GO
CREATE FUNCTION dbo.GetAPPids (@bill_no int,@str varchar(4000))
RETURNS @appids TABLE(bill_number int, appointment_id int)
BEGIN
DECLARE @appid int, @delimeter int, @beginid int, @charcnt int
SET @beginid=1
SET @delimeter = CHARINDEX(',', @str, @beginid)
SET @appid = substring(@str,@beginid,@delimeter-1)
INSERT INTO @appids values(@bill_no, cast(@appid as int))
WHILE CHARINDEX(',', @str, @beginid)>0
BEGIN
SET @beginid = @delimeter+1
SET @delimeter = CHARINDEX(',', @str, @beginid)
If @delimeter<>0
SET @charcnt = @delimeter-@beginid
Else
SET @charcnt = len(@str)-@beginid+1

SET @appid = substring(@str,@beginid,@charcnt)
INSERT INTO @appids values(@bill_no, cast(@appid as int))
END
RETURN
END
GO

select * from dbo.GetAPPids(2010000704,'805807,805808,805809,805810')

sp_help emrtpbilleditems

declare
@bill_no int,
@str varchar(4000);
begin
update emrappointmentdetails set bill_number=(select @bill_no from dbo.GetAPPids where @str='805807,805808,805809,805810')
end

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-07-31 : 04:47:07

hi how to write this oracle query into mssql

MERGE INTO emrappointmentdetails t1
USING emrtpbilleditems t2
ON (t1.appointment_id = t2.appointment_ids)
WHEN MATCHED THEN UPDATE SET t1.bill_number = t2.tp_bill_number
WHEN NOT MATCHED THEN INSERT(appointment_id, patient_id, bill_number)
VALUES(T2.appointment_ids, null, t2.tp_bill_number);

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-31 : 04:49:18

HI
Here formatted query........


MERGE
INTO         emrappointmentdetails t1
USING        emrtpbilleditems t2
ON (
                          t1.appointment_id = t2.appointment_ids
             )
WHEN MATCHED THEN
UPDATE
SET              t1.bill_number = t2.tp_bill_number
WHEN NOT MATCHED THEN
INSERT
       (
              appointment_id,
              patient_id    ,
              bill_number
       )
       VALUES
       (
              T2.appointment_ids,
              NULL              ,
              t2.tp_bill_number
       );

-------------------------
R..

Sachin.Nand

2937 Posts

Posted - 2009-07-31 : 05:16:11

quote:
Originally posted by rajasekhar857

hi how to write this oracle query into mssql

MERGE INTO emrappointmentdetails t1
USING emrtpbilleditems t2
ON (t1.appointment_id = t2.appointment_ids)
WHEN MATCHED THEN UPDATE SET t1.bill_number = t2.tp_bill_number
WHEN NOT MATCHED THEN INSERT(appointment_id, patient_id, bill_number)
VALUES(T2.appointment_ids, null, t2.tp_bill_number);

If you are using SQL 2008 it has this new feature of Merge statement.
http://technet.microsoft.com/en-us/library/bb510625.aspx

PBUH

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-07-31 : 05:46:11

no we are using sql server 2005 only is it fine
Update emrappointmentdetails_bak1
Set bill_number = B.tp_bill_number
from emrappointmentdetails_bak1 A inner join emrtpbilleditems_bak1 B
on cast(A.appointment_id as varchar) = B.appointment_ids
go

if we use like this for merge statement

Sachin.Nand

2937 Posts

Posted - 2009-07-31 : 06:20:41

Yes it will work.

PBUH

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-07-31 : 08:12:51

drop function GetAPPids
GO
CREATE FUNCTION dbo.GetAPPids (@bill_no int,@str varchar(4000))
RETURNS @appids TABLE(bill_number int, appointment_id int)
BEGIN
DECLARE @appid int, @delimeter int, @beginid int, @charcnt int
SET @beginid=1
SET @delimeter = CHARINDEX(',', @str, @beginid)
SET @appid = substring(@str,@beginid,@delimeter-1)
INSERT INTO @appids values(@bill_no, cast(@appid as int))
WHILE CHARINDEX(',', @str, @beginid)>0
BEGIN
SET @beginid = @delimeter+1
SET @delimeter = CHARINDEX(',', @str, @beginid)
If @delimeter<>0
SET @charcnt = @delimeter-@beginid
Else
SET @charcnt = len(@str)-@beginid+1

SET @appid = substring(@str,@beginid,@charcnt)
INSERT INTO @appids values(@bill_no, cast(@appid as int))
END
RETURN
END
GO

hi how can i use an update statement in a function in a generalised way rather than passing a parameter all the time.like in a single update statement i want to update all

Sachin.Nand

2937 Posts

Posted - 2009-07-31 : 09:06:30

uhh..?
I dont think it is possible atleast I dont know any of such a generalised way.

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources