Merge statement with condition

Hi Experts

I have a statement that was created that finds and replaces data from one table to another.  This works quite well and does the trick.  We now need to add condition.  We want to do the update and add that it only be a specific id that is updated.  

The code that we currently use now is:

MERGE INTO [NYP-RetailDataSQL].dbo.[Sales-Comparison0830A]
   USING [NYP-RetailDataSQL].dbo.wholereplace
      ON md_Retailer = [Cust Id]
WHEN MATCHED THEN
   UPDATE
      SET WholesaleID = WHOLEID;


What I need to add is that I only want it to be when the WHOLEID = 120-MW.  

Any help would be greatly appreciated as always.  

A
spudmccAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

awking00Information Technology SpecialistCommented:
I think you can just modify your using statement to a subquery
 USING (SELECT * FROM [NYP-RetailDataSQL].dbo.wholereplace WHERE WHOLEID = 120-MW)
0
spudmccAuthor Commented:
Errored out "syntax error near "on"


MERGE INTO [NYP-RetailDataSQL].dbo.[Sales-Comparison0830A]
   USING(SELECT * FROM [NYP-RetailDataSQL].dbo.wholereplace
           WHERE WholesaleID  = 120 - MW)
  ON md_Retailer = [Cust Id]
WHEN MATCHED THEN UPDATE
SET WholesaleID = WHOLEID;
0
Kevin CrossChief Technology OfficerCommented:
I think you are just missing an alias for the derived table.

MERGE INTO [NYP-RetailDataSQL].dbo.[Sales-Comparison0830A]
   USING(SELECT * FROM [NYP-RetailDataSQL].dbo.wholereplace
           WHERE WholesaleID  = '120 - MW') tfr ON md_Retailer = [Cust Id]
WHEN MATCHED THEN UPDATE
SET WholesaleID = WHOLEID;

You also can use a common table expression.

;WITH tfr AS (
    /* specify columns to retrieve versus asterisk. */
    SELECT *
    FROM [NYP-RetailDataSQL].dbo.wholereplace
    WHERE WholesaleID  = '120 - MW'
)

MERGE INTO [NYP-RetailDataSQL].dbo.[Sales-Comparison0830A]
    USING tfr ON md_Retailer = [Cust Id]
    WHEN MATCHED THEN UPDATE
        SET WholesaleID = WHOLEID;

Regards,

Kevin

EDIT: I think you were missing quotes around the WholesaleID literal also.
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

spudmccAuthor Commented:
SQL1.sql: Error (4,1): Incorrect syntax near the keyword 'WHERE'.
0
Kevin CrossChief Technology OfficerCommented:
Please show the exact SQL you tried that generated the above error. Paste into a code snippet.
0
spudmccAuthor Commented:
;WITH tfr AS (SELECT * FROM WHERE [WholesaleID] = '120-MW')
MERGE INTO [NYP-RetailDataSQL].dbo.[Sales-Comparison0830B]
USING tfr ON md_Retailer = [Cust Id]
WHEN MATCHED THEN UPDATE
SET WholesaleID = WHOLEID;

Open in new window

0
spudmccAuthor Commented:
My knowledge of SQL is very very basic.  I really any solution kept very simple to understand and very easy to implement.  

Thanks all!

A
0
Kevin CrossChief Technology OfficerCommented:
Okay. First, you could have copied and pasted the solution I gave above if the column names where the same.

I wrote:
;WITH tfr AS (
    SELECT *
    FROM [NYP-RetailDataSQL].dbo.wholereplace
    WHERE WholesaleID  = '120-MW'
)
MERGE INTO [NYP-RetailDataSQL].dbo.[Sales-Comparison0830A]
    USING tfr ON md_Retailer = [Cust Id]
    WHEN MATCHED THEN UPDATE
        SET WholesaleID = WHOLEID;

Open in new window


You used:
;WITH tfr AS (SELECT * FROM WHERE [WholesaleID] = '120-MW')
MERGE INTO [NYP-RetailDataSQL].dbo.[Sales-Comparison0830B]
USING tfr ON md_Retailer = [Cust Id]
WHEN MATCHED THEN UPDATE
SET WholesaleID = WHOLEID;

Open in new window


Do you see the difference? you are missing the table name [NYP-RetailDataSQL].dbo.wholereplace, which is why the error is at WHERE because SQL knows that is not a valid table because WHERE is a reserved word.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
spudmccAuthor Commented:
Yes thanks!

I am now getting the following:

SQL1.sql: Error (6,3): Invalid column name 'WholesaleID'.

The column name is WholesaleID and is dragged right from the column list.  Not sure how this could be invalid.
0
spudmccAuthor Commented:
Thank you so much for your patience and knowledge.  Much thanks for your explanation.  it was very helpful and allowed me to finally get this to work.  

Andy
0
Kevin CrossChief Technology OfficerCommented:
No worries. Is WholesaleID a valid column for wholereplace or Sales-Comparison0830B?
If the latter, then the solution is even simpler.

MERGE INTO [NYP-RetailDataSQL].dbo.[Sales-Comparison0830A]
   USING [NYP-RetailDataSQL].dbo.wholereplace
      ON md_Retailer = [Cust Id] AND WholesaleID  = '120-MW'
WHEN MATCHED THEN
   UPDATE
      SET WholesaleID = WHOLEID;
0
Kevin CrossChief Technology OfficerCommented:
It just showed me you accepted the above, so it appears you figured it out. I am glad.

Best regards and happy coding,

Kevin
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.