2) Here is the one you have to be careful with. As with any query I make no promises. Make sure you know what you are doing and have a current backup. Also TEST it before running it on a production database.
If you follow this update script it does the following.
1) Set's patient PatientRaceMid 23126 (In my database this is White)
2) Modifies that row to say last modified by Race Update SQL and the current date/time.
3) Adds a new row for and sets PatientRaceSubCategoryMid 23125 (In my database this is Asian)
4) For that row uses last modified by Race Update SQL and the date/time from the first row it edited to White.
WHERE the PatientRaceMid was 251996 which in my database was White and Asian Race.
This is also set to roll the transaction back. So you'll have to un comment commit and comment out rollback.
BEGIN TRANSACTION
DECLARE @patientRaceSubCategoryMid as INTEGER = 251996; -- White and Asian Race Subcatagory ID
DECLARE @firstHalfRace as INTEGER = 23126; -- White First NEW Race ID
DECLARE @secondHalfRace as INTEGER = 23125; -- Asian Second NEW Race ID
DECLARE @PatientRaceTemp TABLE(
[PatientRaceId] [int] NOT NULL,
[PID] [numeric](19, 0) NOT NULL,
[PatientProfileId] [int] NOT NULL,
[PatientRaceMid] [int] NOT NULL,
[PatientRaceSubCategoryMid] [int] NULL,
[Created] [datetime2](7) NOT NULL,
[CreatedBy] [varchar](30) NOT NULL,
[LastModified] [datetime2](7) NOT NULL,
[LastModifiedBy] [varchar](30) NOT NULL
);
UPDATE [PatientRace]
SET PatientRaceSubCategoryMid = NULL,
PatientRaceMid = @firstHalfRace,
[LastModifiedBy] = 'Race Update SQL',
[LastModified] = GETDATE()
OUTPUT
deleted.[PatientRaceId],
deleted.[PID],
deleted.[PatientProfileId],
@secondHalfRace,
NULL,
deleted.[Created],
deleted.[CreatedBy],
inserted.[LastModified],
inserted.[LastModifiedBy]
INTO @PatientRaceTemp
WHERE PatientRaceSubCategoryMid = @patientRaceSubCategoryMid;
INSERT INTO [PatientRace]
([PID]
,[PatientProfileId]
,[PatientRaceMid]
,[PatientRaceSubCategoryMid]
,[Created]
,[CreatedBy]
,[LastModified]
,[LastModifiedBy])
SELECT [PID]
,[PatientProfileId]
,[PatientRaceMid]
,[PatientRaceSubCategoryMid]
,[Created]
,[CreatedBy]
,[LastModified]
,[LastModifiedBy]
FROM @PatientRaceTemp;
ROLLBACK TRANSACTION
--Commit TRANSACTION
Posted : September 15, 2015 3:37 pm
