We help IT Professionals succeed at work.

question about inner join syntax

brgdotnet
brgdotnet asked
on
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
Comment
Watch Question

Contract ERP Admin/Consultant
Commented:
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.
Top Expert 2012
Commented:
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

dsackerContract ERP Admin/Consultant

Commented:
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.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017
Commented:
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
brgdotnetcontractor

Author

Commented:
Thank you.
Top Expert 2012

Commented:
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.
dsackerContract ERP Admin/Consultant

Commented:
Igyftw.
Top Expert 2012

Commented:
I will have to take your word for it, as I have no idea what that means.