UPDATE from multiple tables 1

[nwm76]

This is giving me a migraine!

I want to update the TDChartFileName column in the following table...

select * from tDPR_WellData

WellID TDChartFileName
------ -----------------------
1 Abaji Okolo 1 268A
2 Ebughu East - 4H 9736
3 Oron West - 1ST1 9795
4 Tiguentourine-325z 9474

...using the ChartName column below:

select * from chart_temp

WellName OpStatID ChartName
-------------- -------- ------------------
Abaji Okolo 1 1000 Abaji Okolo 1 275A
Abaji Okolo 1 1001 Abaji Okolo 1 211C
Ebughu East 4H 1002 Ebughu East - 4H 9750
Ebughu East 4H 1003 Ebughu East - 4H 7621
Oron West - 1ST1 1004 Oron West - 1ST1 9801
Oron West - 1ST1 1005 Oron West - 1ST1 4931
Tiguentourine-325z 1006 Tiguentourine-325z 9324
Tiguentourine-325z 1007 Tiguentourine-325z 1473

The 2 tables are linked by t_Wells:

select * from t_Wells

WellID OpStatID WellName
------ -------- --------------------
1 1000 Abaji Okolo 1
2 1002 Ebughu East 4H
3 1004 Oron West - 1ST1
4 1006 Tiguentourine-325z

I'm sure this could somehow be done in a single statement, but cant figure out how. Hope this makes sense. Any help much appreciated.

Cheers
Neil

 

[JamesLean]

Try this:

UPDATE t1
SET TDChartFileName = t3.chartname
FROM tDPR_WellData t1
  JOIN t_Wells t2 ON t1.wellid = t2.wellid
  JOIN chart_temp t3 ON t2.opstatid = t3.opstatid

--James