Update a single table based on data from multiple

问题:

I need to update table one using data from table two. Table one and two are not related by any common column(s). Table three is related to table two.

Ex : table one(reg_det table)

reg_det_id | reg_id | results
101        | 11     | 344

table two :(temp table)

venue                    | results
Anheim convention center | 355

Table three (regmaster-tbl)

reg_id| venue
11    | Anaheim convention center

I need to update results column in table one using data from table two. But table one and two are not related. Table two and three and table one and three are related as you can see above. Can anyone please suggest any ideas! I need the results value to be 355 in table one and this data is coming from table 2, but these two are unrelated, and they can be related using table three. Sorry if it is confusing!

回答1:

Fairly straight forward:

UPDATE T1
SET result = t2.results
FROM [table one] T1
INNER JOIN [table three] t3
 on t1.reg_id = t3.reg_id
INNER JOIN [table two] T2
 on t2.venue = t3.venue

回答2:

Almost a question instead of an answer. :)

Couldn't you use an implied inner join?

UPDATE rd
   SET rd.results = tt.results
  FROM reg_det rd, regmaster rm, temptable tt
 WHERE rm.reg_id = rd.reg_id
   AND rm.venue = tt.venue;

I find it easier to read, and this syntax works in a SELECT statement, with the same meaning as an explicit inner join.

回答3:

Try this:

UPDATE rd
SET rd.results = t.results
FROM reg_det rd
JOIN regmaster rm ON rm.reg_id = rd.reg_id
JOIN temptable t ON t.venue = rm.venue
WHERE t.results = 355

I added a WHERE clause because otherwise it will update all reg_det records that have matches in regmaster and temptable.