Merge Statement

Question

MERGE DRU As RU   -- Target Table
    USING DPP src   -- Source Table
    ON RU.PP_Id = src.PP_Id 
WHEN MATCHED               
THEN UPDATE 
SET M_Ind ='N'
    WHEN NOT MATCHED
        THEN INSERT    (
                      PP_Id ,
                      R_Cd ,
                      RDesc ,
                      M_Ind,
                      Create_Dt
                    )
        VALUES       (src.PP_Id ,
                      R_Cd ,---------------------------> this value coming from DRU   and i am not able to configure 
                     R_Desc,  ------------------------> this value coming from DRU   and i am not able to configure 
                     src.M_Ind,
                     src.Create_Dt  );-

Answer 1

Your post is somewhat... terse.

I seems that you want to use data from target in the VALUES clause, but this does not make sense. You would not be in this branch, if the row from the source was matched by a row from the target.

So I guess what you want to do is to take data from another row in the target table. In such case the target table must also be part of the source, which thus has to be a table expression of some sort.

But exactly how that query would look like... I think you first need to explain in more detail what you want to achieve.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 2

Where is the DDL?  Did you read the top of this Forum for minimal polite behavior?  

Did you try to qualify the column names? 

MERGE INTO DRU -- Target Table
USING DPP -- Source Table
ON DRU.pp_id = DPP.pp_id
WHEN MATCHED 
THEN UPDATE 
SET DRU.m_ind = 'n' – is this a flag in SQL?? NO! 

WHEN NOT MATCHED
THEN INSERT (pp_id, r_cd, r_desc, m_ind, creation_date)
VALUES (DDP.pp_id, DRU.r_cd, DRU.r_desc, DDP.m_ind, DDP.creation_date);

You did not follow ISO-11179 standards so not data elemtn naem is descriptive (or even readable!), seem to have bit flags and record audit date (creation_date) in the table being audited. These are serious design flaws and errors. 

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

Answer 3

Thanks Erland for reply

situation : R_cd & R_Desc Coming from diff table in to target table ....i means PP_Id in SRC table is identity .... if anything will update/insert it will create new Row in SRC table

and based on that it will search same PP_ID in target table if its in taget table it will there then it will set  M_Ind ='N' else it will update those clm which is cmng form SRC (i.e   PP_Id , M_Ind, Create_Dt ) but this two clm  R_cd & R_Desc is coming from table X so i want to put sreacr criteria on those column that if its match with table x then put that value from that table else put hardcord value

hope u get idea ....

Answer 4

I would recommend posting the DDL and relationships for this one...

Sounds like you need to do as Erland suggested, but joining to table X first rather than the target.  If you make it an outer join then you can set a hardcoded value if the record does not exist in table X by wrapping the column in the ISNULL function.  Basically, replace your source table with a subquery that joins to table X.

Answer 5

Sorry, you have me lost entirely. You may know what is your "diff table" and what PP_id etc stands for, but I do not.

The general recommendation when you want help with something is that you post:

1) CREATE TABLE statements for your tables, preferrably simplified to what is pertinent for the problem at hand.
2) INSERT statements with sample data.
3) The desired result given the sample.
4) A short description of the business problem you are trying to solve.
5) Which version of SQL Server you are using.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 6

hi Erland 

i know what you are saying but its too long to explain thats y i put some part of it where i have difficulty... is it possible for you to give me your email id so i can send you all info?

Answer 7

You can see Erland's e-mail as part of his signature although I doubt he'll appreciate email contacts.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 8

If it's too long to explain, why would it help me to send me e-mail? I would refer you to the forum if you did anyway. I much rather answer questions in public where others can benefit from the answer.

I am sorry, but the challenge with asking about SQL problems is that you need to explain yourself clearly. We can't read your mind. Nor do we know about the system you are working with.

Now, here is a tip: if you sit and write down what you are trying to do and what you have help with in a structured way, you may find that 1) yes, this takes some time 2) when you have completed your post, you already know the answer.

---

Erland Sommarskog, SQL Server MVP, [e-mail]