Help with SQL Query

Hi Experts

I lost several of the saved, working SQL scripts that we currently use when I was migrated to a new machine.  One of the most valuable was one that I was helped with by a super Expert on Experts Exchange.  Problem is now that I had to make a couple of changes and I can't get it to work any longer.  

I have two tables in SQL:   A--Sales Comparison and B-Wholereplace

The two tables are joined by md_retailer (Table A) to custID (Table B).  In Table A we currently have a field called wholesalerID (Table A) that needs to be updated from wholeID (table B).  The main trick is that I only want to change records where the wholesalerID (table A) starts with "120".  

Here is the code that is broken.  

;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;

I am sure that I have things twisted and backwards and that is why I desperately need assistance.  My knowledge of SQL is beginner level at best so please be as simple as possible.  

Thanks so much

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

DcpKingCommented:
Hi Andy,

Make copies of your two tables (if they're huge then just a few hundred records - enough to have a few records that will change). Try this code out on your copies first to see if you've not forgotten anything ...

Reading your description I would have thought that the code would be something like this:
update sc
	set sc.wholesalerID = wr.wholeID
	from [NYP-RetailDataSQL].dbo.[Sales-Comparison0830A] sc
	inner join [NYP-RetailDataSQL].dbo.WholeReplace wr
	on sc.md_retailer = wr.custID
	where substring(sc.wholesalerID, 1, 3) = '120'

Open in new window

which will set the field wholesalerID in SalesComparison to the value of wholeID in WholeReplace for all records sharing the md_retailer = custID link.

The last line constrains the assignment further by only allowing replacement to happen if the value of wholesalerID that will be replaced starts with the characters '120'.

hth

Mike
0
RajnishChoudharyCommented:
Hi Andy,

Please try attached query. I have modified your query to update records in wholereplace from Sales-Comparison0830A, where wholesaleid starts with 120
MERGE [NYP-RetailDataSQL].dbo.wholereplace AS [DESTINATION]
			USING [NYP-RetailDataSQL].dbo.[Sales-Comparison0830A] AS [SOURCE]
			ON (
					[DESTINATION].md_Retailer=[SOURCE].[Cust Id] 
					AND [DESTINATION].WholesaleID  LIKE '120%'--'120-MW'
				)
			WHEN MATCHED THEN 
				UPDATE SET [DESTINATION].WholesaleID = [SOURCE].WHOLEID;

Open in new window


Please revert if you require any further discussion. :-)
Rajnish Kumar
0
PortletPaulfreelancerCommented:
the 'broken code' snippet above uses merge into, and that syntax allows for both updates and inserts

have you looked back through your question history - it's available here
http://www.experts-exchange.com/M_1319289.html
then 'Activity' then 'Questions'

have the table names changed since you got this code here?
have you tried searching?

and arrived back at that accepted answer, here?
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_28227193.html#a39455054
(by: mwvisa1)

Does this help?
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

PortletPaulfreelancerCommented:
sorry I misread your question thought you had lost code from here, my bad.
no points pl.

the 'trick' is to change the where clause to a LIKE condition and use a 'wildcard'.
you can stay with the previous approach using a cte;
or as it is shown above by Rajnish Kumar
they should be functionally the same

;WITH tfr AS (
    SELECT *
    FROM [NYP-RetailDataSQL].dbo.wholereplace
    WHERE WholesaleID  LIKE '120%' -------------------------<<<<<<<<<< trick here
)
MERGE INTO [NYP-RetailDataSQL].dbo.[Sales-Comparison0830A]
    USING tfr ON md_Retailer = [Cust Id]
    WHEN MATCHED THEN UPDATE
        SET WholesaleID = WHOLEID;
0
spudmccAuthor Commented:
I think I am explaining this incorrectly.  The table that needs to be updated is Sales-Comparison with the field wholesalerID.  But I only want to update those records where the wholesalerID starts with 120.  The source table is wholereplace and the field that I want to use as the source is wholeID.  

I've tried the solution from PortletPaul but that updates the Sales-comparison table but still doesn't look for the 120 and updates everything just based on the custID.  

Sorry if I am confusing everyone.
0
RajnishChoudharyCommented:
Hi Andy,

The query what you gave is updating wholereplace, hence modified your query to update same.

   But, If you want to update Sale-comparision
please use below query, which checks 120 as starting string in wholereplace wholesaleid.

MERGE [NYP-RetailDataSQL].dbo.[Sales-Comparison0830A] AS [DESTINATION]
	USING [NYP-RetailDataSQL].dbo.wholereplace AS [SOURCE]
	ON (
			[DESTINATION].[Cust Id] =[SOURCE].md_Retailer 
			AND [SOURCE].WholesaleID  LIKE '120%'
		)
	WHEN MATCHED THEN 
		UPDATE SET [DESTINATION].WHOLEID = [SOURCE].WholesaleID;

Open in new window

0
spudmccAuthor Commented:
Not quite.  I need it to check the string in the Sales-Comparison0830A not the wholereplace table.  Wholereplace contains the new information in field wholeID that I want to update in Sales-Comparison0830A in the wholesalerID field.
0
PortletPaulfreelancerCommented:
Perhaps you could layout all the conditions wanted in one place? Point form is fine.

the question asked:
The main trick is that I only want to change records where the wholesalerID (table A) starts with "120".  

It absorbs a lot of time/effort going back and forth with progressive refinement of what the question is, yours as well as the volunteer experts.
0
spudmccAuthor Commented:
Destination Table [sales-comparison0830A]                Source Table [wholereplace]
Fields                                                                            Fields  
md_retailer                                                                   custID              
wholesalerID                                                                wholeID

The join is between md_retailer and custID.

1---Check destination table for any wholesalerID that starts with "120"
2---Update wholesalerID from wholeID when "120" is confirmed in destination table and md_retailer=custID.  

I apologize if I wasn't being clear.  The last thing I want to do is waste anyone's time.  I respect and appreciate all of your patience, knowledge and time.  I have been a member of Experts Exchange for years and it has been my "go-to" for many questions and issues.
0
DcpKingCommented:
After all the clarification, have you tried out my suggestion? It appears to do what you want:
1. link the two tables on md_retailer and custID
2. update wholesalerID from wholeID
3. only do the update if the destination already starts with "120'

update sc
	set sc.wholesalerID = wr.wholeID
	from [NYP-RetailDataSQL].dbo.[Sales-Comparison0830A] sc
	inner join [NYP-RetailDataSQL].dbo.WholeReplace wr
	on sc.md_retailer = wr.custID
	where substring(sc.wholesalerID, 1, 3) = '120'

Open in new window


Mike
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:
This worked perfectly!  Thank you again for all of your patience and knowledge.  This is greatly appreciated as always.  I know you all are volunteers and don't have to do this for us.  

A
0
DcpKingCommented:
Thanks, spudmcc. It just looked like the code you had was way more complicated that it had to be to get the results you were asking for, hence me asking you to test it on sample data first.
Yes, we're all volunteers - some of us do it for fun, and others to look and learn from our peers.

Mike
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
Query Syntax

From novice to tech pro — start learning today.