CODEWITHSUNDEEP
sqlsql-servert-sql
bumble_bee_tuna
bumble_bee_tuna

Reputation: 3563

UPDATE ACROSS JOIN SYNTAX

I am trying to get an update statement to function how I need. I've tried the following with INNER / OUTER LEFT / RIGHT JOINS. I continue to have records that are not updated in the usertype2 table despite having usertype3 child values. Am I going about this the right way? Usertype2 and Usertype3 will always have a mat_id parent value, matter is always parent and usertype2 and usertype3 are child records.

UPDATE f
SET f.USR1_05_01 = expTot
FROM lntmuid.usertype2 f JOIN
(
    SELECT MAX(e.mat_id) AS matId, SUM(CONVERT(money, e.USR1_02_08)) AS expTot 
    FROM TimeMatters.lntmuid.usertype3 e 
    GROUP BY mat_id
)
e ON f.mat_id = e.matId
WHERE f.mat_id = e.matId

Upvotes: 0

Views: 53

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You can write the query as:

UPDATE f
    SET f.USR1_05_01 = e.expTot
FROM lntmuid.usertype2 f JOIN
     (SELECT e.mat_id, SUM(CONVERT(money, e.USR1_02_08)) AS expTot 
      FROM TimeMatters.lntmuid.usertype3 e 
      GROUP BY mat_id
     ) e
     ON f.mat_id = e.matId;

However, your original query should do what you want.

Upvotes: 1

Related Questions