question about inner join syntax

I have been given this pseudo code in order to write my query. The pseudo code is from someone who is not really strong at SQL, however this persons hase a pretty good idea of what she wants.
So here is the pseudo code.
UPDATE Customer
SET Customer.Id = CopyCust.Id,
    Customer.IsCurrent = CopyCust.IsCurrent
   FROM Customer
   INNER JOIN CopyCust
  ON
Customer.Code = CopyCust.Code
Customer.SSN  = CopyCust.SSN
Customer.LastName = Customer.FirstName

I don't think you can join on several columns at one time. So, What I really think she wanst is the following solution: (What do you think Experts)

UPDATE Customer
SET Customer.Id = CopyCust.Id,
    Customer.IsCurrent = CopyCust.IsCurrent
FROM Customer
INNER JOIN CopyCust
ON
Customer.Code = CopyCust.Code
WHERE
Customer.SSN  = CopyCust.SSN
AND Customer.LastName = Customer.FirstName
LVL 2
brgdotnetcontractorAsked:
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.

dsackerContract ERP Admin/ConsultantCommented:
Actually you can join on many columns, but you would separate them with AND statements, as follows:
UPDATE Customer
SET Customer.Id = CopyCust.Id,
    Customer.IsCurrent = CopyCust.IsCurrent
FROM Customer
INNER JOIN CopyCust
  ON  CopyCust.Code = Customer.Code
  AND CopyCust.SSN  = Customer.SSN
WHERE Customer.LastName = Customer.FirstName

Open in new window

I put line 8 into its own WHERE clause, since it has nothing to do with the CopyCust table.
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
Anthony PerkinsCommented:
You should get in the habit of using aliases as in:
UPDATE  c
SET     Id = cc.Id,
        IsCurrent = cc.IsCurrent
FROM    Customer c
        INNER JOIN CopyCust cc ON c.Code = cc.Code
                                  AND c.SSN = cc.SSN
WHERE   c.LastName = c.FirstName

Open in new window

0
dsackerContract ERP Admin/ConsultantCommented:
Brgdotnet, for small queries (and optionally for larger one), you can lose readability with aliases. They have their place, but for what you're doing, your original code was clean, easy to read and easy to advise on what to fine-tune.

I suggest you stay with your aesthetic and clean way of writing code, unless you decide for yourself to use abbreviations as aliases. They buy you nothing besides trading readability for keystrokes.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Vitor MontalvãoMSSQL Senior EngineerCommented:
As it can be used all conditions in the INNER JOIN clause, separating them with the AND operator:
INNER JOIN CopyCust   ON
 Customer.Code = CopyCust.Code
AND Customer.SSN  = CopyCust.SSN
AND Customer.LastName = Customer.FirstName


I would prefer to leave in the INNER JOIN only the relationship between tables (TableA.PK = TableB.FK) and in the WHERE clause the search criteria. The query plan will be the same but I think give you a better idea when you see the command. So, your suggestion may be right:
INNER JOIN CopyCust
 ON  Customer.Code = CopyCust.Code
'--> Relationship of the tables
 WHERE  Customer.SSN  = CopyCust.SSN AND Customer.LastName = Customer.FirstName '--> Search criteria
0
brgdotnetcontractorAuthor Commented:
Thank you.
0
Anthony PerkinsCommented:
They buy you nothing besides trading readability for keystrokes.
We will have to agree to disagree.  In my view the version I posted is far more readable.
0
dsackerContract ERP Admin/ConsultantCommented:
Igyftw.
0
Anthony PerkinsCommented:
I will have to take your word for it, as I have no idea what that means.
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.