none
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  );-
    Friday, January 04, 2013 10:02 PM

Answers

  • 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.

    • Proposed as answer by Naomi NModerator Monday, January 07, 2013 4:39 PM
    • Marked as answer by SAM_1983 Friday, January 11, 2013 7:10 PM
    Monday, January 07, 2013 3:25 PM

All replies

  • 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, esquel@sommarskog.se
    Friday, January 04, 2013 10:47 PM
  • 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

    Saturday, January 05, 2013 1:19 AM
  • 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 ....

    Monday, January 07, 2013 2:36 PM
  • 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.

    • Proposed as answer by Naomi NModerator Monday, January 07, 2013 4:39 PM
    • Marked as answer by SAM_1983 Friday, January 11, 2013 7:10 PM
    Monday, January 07, 2013 3:25 PM
  • 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, esquel@sommarskog.se
    Monday, January 07, 2013 10:55 PM
  • 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?

    Tuesday, January 08, 2013 2:30 PM
  • 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

    Tuesday, January 08, 2013 4:02 PM
  • 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, esquel@sommarskog.se
    Tuesday, January 08, 2013 11:00 PM