syntax for inner query used as JOIN (sql 2012)

is there any better/more efficient way to do the below update in newer versions?
thx-
~~~
       UPDATE maintable
SET      maintable_columntobeUpdated      = InnerTable.[UpdateSource]
FROM
(SELECT
        MainTableInsideInnerTable.MainTableInsideInnerTablePK                  AS InnerTableColumnFK
      , TableFromWhereUpdateIsComingFrom.Column_XYZ            
      , TableFromWhereUpdateIsComingFrom.TableFromWhereUpdateIsComingFrom_ColumnWhichIsUsedToUpdateMaintable      AS [UpdateSource]
 FROM
       TableFromWhereUpdateIsComingFrom
      
      INNER JOIN maintable MainTableInsideInnerTable
      ON TableFromWhereUpdateIsComingFrom.Column_ABC = MainTableInsideInnerTable.Column_ABC
  AND TableFromWhereUpdateIsComingFrom.Column_XYZ = MainTableInsideInnerTable.Column_XYZ)
  InnerTable
WHERE
      maintable.maintablePK = InnerTable.InnerTableColumnFK
LVL 5
25112Asked:
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.

chaauCommented:
Your query is as good as. You can change it to improve readability:
;WITH innerTable as 
(SELECT
        MainTableInsideInnerTable.MainTableInsideInnerTablePK                  AS InnerTableColumnFK
      , TableFromWhereUpdateIsComingFrom.Column_XYZ            
      , TableFromWhereUpdateIsComingFrom.TableFromWhereUpdateIsComingFrom_ColumnWhichIsUsedToUpdateMaintable      AS [UpdateSource]
 FROM
       TableFromWhereUpdateIsComingFrom
      
      INNER JOIN maintable MainTableInsideInnerTable
      ON TableFromWhereUpdateIsComingFrom.Column_ABC = MainTableInsideInnerTable.Column_ABC
  AND TableFromWhereUpdateIsComingFrom.Column_XYZ = MainTableInsideInnerTable.Column_XYZ) 
UPDATE maintable
SET      maintable_columntobeUpdated      = InnerTable.[UpdateSource]
WHERE
      maintable.maintablePK = InnerTable.InnerTableColumnFK 

Open in new window

In addition to that you may use the MERGE query if you also need to insert or delete some records at the same time:
MERGE maintable
USING (SELECT
        MainTableInsideInnerTable.MainTableInsideInnerTablePK                  AS InnerTableColumnFK
      , TableFromWhereUpdateIsComingFrom.Column_XYZ            
      , TableFromWhereUpdateIsComingFrom.TableFromWhereUpdateIsComingFrom_ColumnWhichIsUsedToUpdateMaintable      AS [UpdateSource]
 FROM
       TableFromWhereUpdateIsComingFrom
      
      INNER JOIN maintable MainTableInsideInnerTable
      ON TableFromWhereUpdateIsComingFrom.Column_ABC = MainTableInsideInnerTable.Column_ABC
  AND TableFromWhereUpdateIsComingFrom.Column_XYZ = MainTableInsideInnerTable.Column_XYZ) AS innerTable
ON  maintable.maintablePK = InnerTable.InnerTableColumnFK 
WHEN MATCHED THEN
 UPDATE SET      maintable_columntobeUpdated      = InnerTable.[UpdateSource]
WHEN NOT MATCHED THEN
    INSERT (columntobeUpdated) -- here you can also insert other values
    VALUES (InnerTable.[UpdateSource])  -- here you can also insert other values

Open in new window

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
25112Author Commented:
chaau, thats good to know alternatives...
do you see execution (performance) benefits when done using the new method mentioned?
0
chaauCommented:
As I said before the CTE method will most likely just improve the readability. In my opinion there is no reason to change the existing code. It is a good idea to learn this technique for use in the new projects though, as it makes the code easier to read.
As for the MERGE statement: it will make a big difference for the situations when you need the INSERT/UPDATE (which is BTW called UPSERT) in one operation.
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.