Update from staging table with multiple registers repeated

There are an AR table and an AR_STAGE table (samples) like this:

DECLARE @AR AS TABLE (
    Patient VARCHAR(50),
    MRN INT,
    Submission DATETIME,
    Active VARCHAR(5),
    Last_update DATETIME
);
INSERT INTO @AR
VALUES
('Zaczeniuk, Ann',  1338728,    null,   'NEW',  null),
('Zaczeniuk, Ann',  1338728,    null,   'NEW',null),
('Palomino, Joseph',717463, '10/09/2023',   'NEW',  null),
('Palomino, Joseph',717463, '10/09/2023',   'NEW',  null),
('Linares, Irma',   3028394,    null,   'NEW',  null),
('Linares, Irma',   3028394,    null,   'NEW',  null),
('Imbery, Layne',   2910744,    null,   'NEW',  null),
('Imbery, Layne',   2910744,    null,   'NEW',  null);

SELECT *
FROM @ar

AR

Patient MRN Submission Active Last_update
Zaczeniuk, Ann 1338728 null NEW null
Zaczeniuk, Ann 1338728 null NEW null
Palomino, Joseph 717463 10/09/2023 NEW null
Palomino, Joseph 717463 10/09/2023 NEW null
Linares, Irma 3028394 NEW null
Linares, Irma 3028394 NEW null
Imbery, Layne 2910744 null NEW null
Imbery, Layne 2910744 null NEW null

AR_STAGE

Patient MRN Submission Active
Zaczeniuk, Ann 1338728 07/09/2023 NEW
Palomino, Joseph 717463 10/09/2023 NEW
Palomino, Joseph 717463 10/09/2023 NEW
Linares, Irma 3028394 NEW
Linares, Irma 3028394 NEW
Imbery, Layne 2910744 null NEW
Imbery, Layne 2910744 null NEW

I need to update my AR table with the information of the AR_STAGE, mapping by MRN. I only need to update the records in AR when Submission is in blank or null and are as date value in AR_STAGE, then changing Active= UPDATED. So, the others must be stay as Active=NEW, even if the AR_STAGE.Submission field has a null/blank value.

DESIRED OUTPUT AR TABLE

Patient MRN Submission Active Last_update
Zaczeniuk, Ann 1338728 07/09/2023 UPDATED 2023-09-15 20:13:27.4066667
Zaczeniuk, Ann 1338728 07/09/2023 UPDATED 2023-09-15 20:13:27.4066667
Palomino, Joseph 717463 10/09/2023 NEW null
Palomino, Joseph 717463 10/09/2023 NEW null
Linares, Irma 3028394 NEW null
Linares, Irma 3028394 NEW null
Imbery, Layne 2910744 null NEW null
Imbery, Layne 2910744 null NEW null

I tried this query:

MERGE AR AS Target
USING 
  ( SELECT MRN, Submission, COUNT(*) AS A
  FROM AR_STAGE GROUP BY MRN,Submission )  Source
ON Source.MRN = TARGET.MRN
WHEN MATCHED AND  IsNull(Target.[Submission], '') = ''  THEN
UPDATE SET 
Target.[Submission] = Source.[Submission]
,Target.[Last_update] = CURRENT_TIMESTAMP
,Target.Active="UPDATED"

But it brought a wrong output:

WRONG OUTPUT AR TABLE

Patient MRN Submission Active Last_update
Zaczeniuk, Ann 1338728 07/09/2023 UPDATED 2023-09-15 20:13:27.4066667
Zaczeniuk, Ann 1338728 07/09/2023 UPDATED 2023-09-15 20:13:27.4066667
Palomino, Joseph 717463 10/09/2023 NEW null
Palomino, Joseph 717463 10/09/2023 NEW null
Linares, Irma 3028394 UPDATED 2023-09-15 20:13:27.4066667
Linares, Irma 3028394 UPDATED 2023-09-15 20:13:27.4066667
Imbery, Layne 2910744 null UPDATED 2023-09-15 20:13:27.4066667
Imbery, Layne 2910744 null UPDATED 2023-09-15 20:13:27.4066667

Because the last four records should’ve been Active=NEW with Last_update= null. This because there is no change on submission.

  • Please provide a data schema or table descriptions so we know what the data is example:(probably wrong) DECLARE @AR AS TABLE ( Patient VARCHAR(50), MRN INT, Submission DATETIME, Active VARCHAR(5), Last_update DATETIME ); Probably needs different on Submission for sure but hard to tell from the data

    – 




  • To help you get started I created a BAD sample that you can update properly – one for the AR_STAGE would be good also to have

    – 

  • Presumably you need to add a condition to the query in your using clause to account for the ” and are as date value in AR_STAGE” requirement. Maybe FROM AR_STAGE WHERE IsNull([Submission], '') <> '' although I don’t understand the purpose of the group by there.

    – 

  • Let me know if this work dbfiddle.uk/uf4y5P_U

    – 

  • It would be better to put that Fiddle in the question rather than as a comment. Is this what you want? dbfiddle.uk/8EboD8nP

    – 

Leave a Comment