Update is changing every row in my Table to be the same!

[SimonBurrell]

Hi All

I am trying to write a query that (in basic terms) checks if rows in newtable exist in oldtable and if this is true then update oldtable row with new data, else insert data from newtable to oldtable in a new row.

I need to update three tables Patient, SSI_Procedure_Detail and SSI_Infection_details from the new table called RMHResult.

The result so-far is that every row that exists in the SSI_Infection_details table ends up getting replaced by a single row (duplicated for every existing row).

I would really appreciate some tips on how to achieve this from some of you experts out there, as I feel I have complicated things!!!

Thanks heaps in Advance.

Over my head!!!!

Simon


My query so-far is....... (please ignore stuff that is commented out, as this is to remind me to do stuff later)

--***********************

/* Update or Insert into Patient Table*/

IF EXISTS (SELECT Patient_UR_NO FROM Patient p JOIN
RMHResults r ON LEFT (r.URNO, len(r.URNO)-3) = p.Patient_UR_NO
WHERE (LEFT (r.URNO, len(r.URNO)-3)) = p.Patient_UR_No)
BEGIN

update Patient
Set --[Patient_ID]= ,
[Patient_UR_No]= (LEFT (r.URNO, len(r.URNO)-3)),
[Hosp_ID]= (RIGHT (r.URNO,3)),
[Patient_Sex]= r.SEX,
[Patient_DOB]= r.DOB
FROM RMHResults r
JOIN Patient p ON LEFT (r.URNO, len(r.URNO)-3) = p.Patient_UR_NO
WHERE (RIGHT (r.URNO,3)) = p.HOSP_ID AND
(LEFT (r.URNO, len(r.URNO)-3)) = p.Patient_UR_No
END

ELSE
IF NOT EXISTS (SELECT Patient_UR_NO FROM Patient p JOIN
RMHResults r ON LEFT (r.URNO, len(r.URNO)-3) = p.Patient_UR_NO
WHERE (LEFT (r.URNO, len(r.URNO)-3)) = p.Patient_UR_No)

BEGIN

DECLARE @URNO VARCHAR (50),
@SEX CHAR (10),
@DOB DATETIME
SELECT @URNO = URNO FROM RMHResults JOIN Patient ON (LEFT (URNO, len(URNO)-3)) = Patient_UR_No
WHERE (RIGHT (URNO,3)) = HOSP_ID AND
(LEFT (URNO, len(URNO)-3)) = Patient_UR_No
SELECT @SEX = SEX FROM RMHResults JOIN Patient ON (LEFT (URNO, len(URNO)-3)) = Patient_UR_No
WHERE (RIGHT (URNO,3)) = HOSP_ID AND
(LEFT (URNO, len(URNO)-3)) = Patient_UR_No
SELECT @DOB = DOB FROM RMHResults JOIN Patient ON (LEFT (URNO, len(URNO)-3)) = Patient_UR_No
WHERE (RIGHT (URNO,3)) = HOSP_ID AND
(LEFT (URNO, len(URNO)-3)) = Patient_UR_No

INSERT INTO [Patient]([Patient_ID],
[Patient_UR_No],
[Hosp_ID],
[Patient_Sex],
[Patient_DOB])
VALUES ( (NEWID ()),
(LEFT (@URNO, len(@URNO)-3)),
(RIGHT (@URNO,3)),
@SEX,
@DOB)

END

/* Update or Insert into SSI_Procedure_Detail Table*/


IF EXISTS (SELECT Patient_UR_NO FROM Patient p JOIN
RMHResults r ON LEFT (r.URNO, len(r.URNO)-3) = p.Patient_UR_NO
WHERE (LEFT (r.URNO, len(r.URNO)-3)) = p.Patient_UR_No)
BEGIN


UPDATE SSI_Procedure_Detail
SET --[SSI_Procedure_ID]=,
[SSI_Proc_Date]= r.Proc_Date,
[SSI_Admit_Date]=r.Admit_Date,
[SSI_Discharge_Date]=r.Discharge_Date,
[SSI_Proc_Start]=r.Proc_Start,
[SSI_Proc_End]=r.Proc_End,
[SSI_ASA]=r.SSI_ASA,
[SSI_Implant]=r.Implant,
[SSI_Emergency]=r.Emergency,
--[SSI_Procedure_Risk]=<SSI_Procedure_Risk,varchar(10),>,
--[SSi_Surgeon_Code]=,
[Patient_ID]= p.Patient_ID,
[Procedure_ID]=r.[Procedure],
[Wound_Cl_ID]=r.W_Class,
[Speciality_Id]=r.Specialty,
[SSI_Infect_Detected]=r.Infection_Detected
FROM RMHResults r
JOIN Patient p ON LEFT (r.URNO, len(r.URNO)-3) = p.Patient_UR_NO
WHERE (RIGHT (r.URNO,3)) = p.HOSP_ID AND
(LEFT (r.URNO, len(r.URNO)-3)) = p.Patient_UR_No
END

ELSE
IF NOT EXISTS (SELECT Patient_UR_NO FROM Patient p JOIN
RMHResults r ON LEFT (r.URNO, len(r.URNO)-3) = p.Patient_UR_NO
WHERE (LEFT (r.URNO, len(r.URNO)-3)) = p.Patient_UR_No)

BEGIN

DECLARE @PDATE DATETIME, @ADate DATETIME, @DDate VARCHAR (50), @STime Datetime, @ETime DATETIME,
@ASA Numeric (4,0), @Implant SMALLINT, @Emerg SMALLINT, @SCode NUMERIC (4,0),
@Patient CHAR (50), @Proc VARCHAR (50), @WClass NUMERIC (4,0),
@Specialty NUMERIC (4,0), @Infection BIT

SELECT @PDate = Proc_Date FROM RMHResults JOIN Patient ON (LEFT (URNO, len(URNO)-3)) = Patient_UR_No
WHERE (RIGHT (URNO,3)) = HOSP_ID AND
(LEFT (URNO, len(URNO)-3)) = Patient_UR_No
SELECT @ADate =Admit_Date FROM RMHResults JOIN Patient ON (LEFT (URNO, len(URNO)-3)) = Patient_UR_No
WHERE (RIGHT (URNO,3)) = HOSP_ID AND
(LEFT (URNO, len(URNO)-3)) = Patient_UR_No
SELECT @DDate = Discharge_Date FROM RMHResults JOIN Patient ON (LEFT (URNO, len(URNO)-3)) = Patient_UR_No
WHERE (RIGHT (URNO,3)) = HOSP_ID AND
(LEFT (URNO, len(URNO)-3)) = Patient_UR_No
SELECT @STime = Proc_Start FROM RMHResults JOIN Patient ON (LEFT (URNO, len(URNO)-3)) = Patient_UR_No
WHERE (RIGHT (URNO,3)) = HOSP_ID AND
(LEFT (URNO, len(URNO)-3)) = Patient_UR_No
SELECT @ETime = Proc_End FROM RMHResults JOIN Patient ON (LEFT (URNO, len(URNO)-3)) = Patient_UR_No
WHERE (RIGHT (URNO,3)) = HOSP_ID AND
(LEFT (URNO, len(URNO)-3)) = Patient_UR_No
SELECT @ASA = SSI_ASA FROM RMHResults JOIN Patient ON (LEFT (URNO, len(URNO)-3)) = Patient_UR_No
WHERE (RIGHT (URNO,3)) = HOSP_ID AND
(LEFT (URNO, len(URNO)-3)) = Patient_UR_No
SELECT @Implant = [Implant] FROM RMHResults JOIN Patient ON (LEFT (URNO, len(URNO)-3)) = Patient_UR_No
WHERE (RIGHT (URNO,3)) = HOSP_ID AND
(LEFT (URNO, len(URNO)-3)) = Patient_UR_No
SELECT @Emerg = Emergency FROM RMHResults JOIN Patient ON (LEFT (URNO, len(URNO)-3)) = Patient_UR_No
WHERE (RIGHT (URNO,3)) = HOSP_ID AND
(LEFT (URNO, len(URNO)-3)) = Patient_UR_No
-- SELECT @Scode = [Surgeon] FROM RMHResults JOIN Patient ON (LEFT (URNO, len(URNO)-3)) = Patient_UR_No
-- WHERE (RIGHT (URNO,3)) = HOSP_ID AND
-- (LEFT (URNO, len(URNO)-3)) = Patient_UR_No
SELECT @Patient = Patient_UR_No FROM Patient JOIN RMHResults ON (LEFT (URNO, len(URNO)-3)) = Patient_UR_No
WHERE (RIGHT (URNO,3)) = HOSP_ID AND
(LEFT (URNO, len(URNO)-3)) = Patient_UR_No
SELECT @Proc = [Procedure] FROM RMHResults JOIN Patient ON (LEFT (URNO, len(URNO)-3)) = Patient_UR_No
WHERE (RIGHT (URNO,3)) = HOSP_ID AND
(LEFT (URNO, len(URNO)-3)) = Patient_UR_No
SELECT @WClass = W_Class FROM RMHResults JOIN Patient ON (LEFT (URNO, len(URNO)-3)) = Patient_UR_No
WHERE (RIGHT (URNO,3)) = HOSP_ID AND
(LEFT (URNO, len(URNO)-3)) = Patient_UR_No
SELECT @Specialty = [Specialty] FROM RMHResults JOIN Patient ON (LEFT (URNO, len(URNO)-3)) = Patient_UR_No
WHERE (RIGHT (URNO,3)) = HOSP_ID AND
(LEFT (URNO, len(URNO)-3)) = Patient_UR_No
SELECT @Infection = Infection_Detected FROM RMHResults JOIN Patient ON (LEFT (URNO, len(URNO)-3)) = Patient_UR_No
WHERE (RIGHT (URNO,3)) = HOSP_ID AND
(LEFT (URNO, len(URNO)-3)) = Patient_UR_No


INSERT INTO [SSI_Procedure_Detail]([SSI_Procedure_ID],
[SSI_Proc_Date],
[SSI_Admit_Date],
[SSI_Discharge_Date],
[SSI_Proc_Start],
[SSI_Proc_End],
[SSI_ASA],
[SSI_Implant],
[SSI_Emergency],
--[SSI_Procedure_Risk],
[SSi_Surgeon_Code],
[Patient_ID],
[Procedure_ID],
[Wound_Cl_ID],
[Speciality_Id],
[SSI_Infect_Detected])
VALUES((NEWID()),
@PDATE,
@ADate,
@DDate,
@STime,
@ETime,
@ASA,
@Implant,
@Emerg,
@SCode,
@Patient,
@Proc ,
@WClass ,
@Specialty,
@Infection )



END

/* Update or Insert into SSI_Infection_Details Table*/

-- not yet written --

--****************************************

 

[sguslan]

Am i missing something?
You mentioned that SSI_Infection_details is being updated erroneuosly, but i do not see any update against SSI_Infection_details in your code!

 

[SimonBurrell]

OOPS! Sorry that line should read...

..every row that exists in the SSI_Procedure_details table ends up getting replaced by a single row (duplicated for every existing row).

Sorry for the confusion.

Simon

 

[sguslan]

Do one thing:

Select
(
select count(*) from SSI_Procedure_Detail
)
-
(
select count(*) FROM RMHResults r
JOIN Patient p ON LEFT (r.URNO, len(r.URNO)-3) =
p.Patient_UR_NO
WHERE (RIGHT (r.URNO,3)) = p.HOSP_ID AND
(LEFT (r.URNO, len(r.URNO)-3)) = p.Patient_UR_No
)


If the result is 0 then you were correct, it is updating all records in SSI_Procedure_Detail.

Now investigate this part of the code:

JOIN Patient p ON LEFT (r.URNO, len(r.URNO)-3) =
p.Patient_UR_NO
WHERE (RIGHT (r.URNO,3)) = p.HOSP_ID AND
(LEFT (r.URNO, len(r.URNO)-3)) = p.Patient_UR_No

I can not tell if it is correctly done because we do not know the how data stored in the p.Patient_UR_No,r.URNO andp.HOSP_ID are structured, i.e. what is the meaning of the each digit in the fields. May be your code's LEFT or RIGHT functions were passed the wrong values ...