CODEWITHSUNDEEP
sqlsql-server
BishtDev
BishtDev

Reputation: 33

SQL Server object creation

UPDATE 
    TABLE_TA                                           
SET  
    ABM=A.ABM
FROM 
    (SELECT ID,ABM FROM FINAL_DATA )A                                          
INNER JOIN 
    TABLE_TA TB                                           
ON 
    A.ID=TB.ID

Someone who wrote this query in a procedure has left the company. This query is confusing me a bit about the table TABLE_TA can anyone tell me if this table's object will be created two times or one? The concern in query is in inner join he used Alias [TB] however updating table.

PS: Query may not be written logically correct as no need for an extra join however the point is creation of object as of now.

Upvotes: 0

Views: 72

Answers (1)

user4622594
user4622594

Reputation:

The query you posted uses a "DERIVED TABLE" named "A". Derived Tables are Resultsets that you can use like a table. Have a look at this article to find out how they work: http://www.programmerinterview.com/index.php/database-sql/derived-table-vs-subquery/

I think the better written version of the statement you have is this:

UPDATE  TB
SET     TB.ABM = A.ABM
FROM    FINAL_DATA A
        INNER JOIN TABLE_TA TB ON A.ID = TB.ID

To explain: your query uses two tables (a derived table "A" and TABLE_TA "TB")..so the first line should be UPDATE TB instead of UPDATE TABLE_TA. What's more it's not necessary at all to use a derived table here. Simple join is enough.

Upvotes: 1

Related Questions