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.[S
USING [NYP-RetailDataSQL].dbo.wh
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
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.[S
USING [NYP-RetailDataSQL].dbo.wh
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
ASKER
Errored out "syntax error near "on"
MERGE INTO [NYP-RetailDataSQL].dbo.[S
USING(SELECT * FROM [NYP-RetailDataSQL].dbo.wh
WHERE WholesaleID = 120 - MW)
ON md_Retailer = [Cust Id]
WHEN MATCHED THEN UPDATE
SET WholesaleID = WHOLEID;
MERGE INTO [NYP-RetailDataSQL].dbo.[S
USING(SELECT * FROM [NYP-RetailDataSQL].dbo.wh
WHERE WholesaleID = 120 - MW)
ON md_Retailer = [Cust Id]
WHEN MATCHED THEN UPDATE
SET WholesaleID = WHOLEID;
I think you are just missing an alias for the derived table.
MERGE INTO [NYP-RetailDataSQL].dbo.[S
USING(SELECT * FROM [NYP-RetailDataSQL].dbo.wh
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.wh
WHERE WholesaleID = '120 - MW'
)
MERGE INTO [NYP-RetailDataSQL].dbo.[S
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.
MERGE INTO [NYP-RetailDataSQL].dbo.[S
USING(SELECT * FROM [NYP-RetailDataSQL].dbo.wh
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.wh
WHERE WholesaleID = '120 - MW'
)
MERGE INTO [NYP-RetailDataSQL].dbo.[S
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.
ASKER
SQL1.sql: Error (4,1): Incorrect syntax near the keyword 'WHERE'.
Please show the exact SQL you tried that generated the above error. Paste into a code snippet.
ASKER
;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;ASKER
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
Thanks all!
A
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER
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
Andy
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.[S
USING [NYP-RetailDataSQL].dbo.wh
ON md_Retailer = [Cust Id] AND WholesaleID = '120-MW'
WHEN MATCHED THEN
UPDATE
SET WholesaleID = WHOLEID;
If the latter, then the solution is even simpler.
MERGE INTO [NYP-RetailDataSQL].dbo.[S
USING [NYP-RetailDataSQL].dbo.wh
ON md_Retailer = [Cust Id] AND WholesaleID = '120-MW'
WHEN MATCHED THEN
UPDATE
SET WholesaleID = WHOLEID;
It just showed me you accepted the above, so it appears you figured it out. I am glad.
Best regards and happy coding,
Kevin
Best regards and happy coding,
Kevin
USING (SELECT * FROM [NYP-RetailDataSQL].dbo.wh