CODEWITHSUNDEEP
sql-server-2008merge
dbinott
dbinott

Reputation: 911

Using MERGE in SQL Server 2008

I just found out about this nifty little feature. I have a couple questions. consider the statement below.

This is how interpret how it works. The USING statement is what gets compared to see if there is a match correct? I want to use how it is now, but I want to use 2 other columns from the source table in the MATCH portion. I can't do that. So is there a way that I can use the 2 columns (decesed (I know its spelled wrong :) ) and hicno_enc)?
Another thing I would like to do and don't know if it possible, but if the row exists in target but not source, then mark it inactive.

SELECT FIRST_NAME, LAST_NAME, SEX1, BIRTH_DATE
FROM 
    aco.tmpimport i
    INNER JOIN aco.patients p 
    ON p.hicnoenc = i.hicno_enc
    MERGE aco.patients AS target
    USING (
            SELECT FIRST_NAME, LAST_NAME, SEX1, BIRTH_DATE 
            FROM aco.tmpimport
          ) AS source
    ON target.hicnoenc = source.hicno_enc
    WHEN MATCHED AND target.isdeceased <> CONVERT(BIT,source.decesed) THEN 
        UPDATE 
            SET 
                target.isdeceased = source.decesed, 
                updatedat = getdate(), 
                updatedby = 0
    WHEN NOT MATCHED THEN
        INSERT (firstname, lastname, gender, dob, isdeceased, hicnoenc)
        VALUES (source.FIRST_NAME, 
                source.LAST_NAME, 
                source.sex1, 
                source.BIRTH_DATE, 
                source.decesed, 
                source.hicno_enc);

Upvotes: 1

Views: 1556

Answers (1)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

So is there a way that I can use the 2 columns (decesed (I know its spelled wrong :) ) and hicno_enc)?

Add the columns you need in the select statement in the using clause.

USING (
        SELECT FIRST_NAME, LAST_NAME, SEX1, BIRTH_DATE, decesed, hicno_enc 
        FROM aco.tmpimport
      ) AS source

if the row exists in target but not source, then mark it inactive.

Add a when not matched by source clause and do the update.

WHEN NOT MATCHED BY SOURCE THEN
    UPDATE 
        SET active = 0

Upvotes: 2

Related Questions