• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 98
  • Last Modified:

MS SQL Multi Joins Update/Select

Hi,
I have 3 tables structured as follows
table1 (equipnum, field1, field2)  (this table is used for reporting
table2 (projectID, equipnum, field11, field21)
table3 (projectID, equipnum, field111, field211)

I want to update table1 with values from table2 and table3
The join key table1-table2 : equipnum, table2-table3: projectID, equipnum

Regards
0
Omer-Pitou
Asked:
Omer-Pitou
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
0
 
Vikas GargBusiness Intelligence DeveloperCommented:
Hello,

You can try something like this

UPDATE Table1
SET Col2 = t2.Col2,
Col3 = t2.Col3
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.Col1 = t2.Col1
WHERE t1.Col1 IN (21, 31)
GO

Open in new window

0
 
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
@Vikas: Your solution will work, only catch is that using UPDATE Table1.... FROM Table1 t1 may cause the table to block itself in an environment that supports parallelism. A better alternate is to use UPDATE t1....FROM Table1 t1 as shown below:

UPDATE t1
SET t1.field1 = t2.field11 - field111,
    t1.field2 = t2.field21 - field211
FROM dbo.table1 AS t1
INNER JOIN dbo.table2 AS t2 ON t1.equipnum = t2.equipnum
INNER JOIN dbo.table3 AS t3 ON t2.projectID = t3.projectID
                           AND t2.equipnum = t3.equipnum;

Open in new window

0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now