CODEWITHSUNDEEP
sqlsql-server-2008sql-server-2005
user1984533
user1984533

Reputation: 161

Update a single table based on data from multiple tables SQL Server 2005,2008

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!

Upvotes: 16

Views: 87777

Answers (3)

Jesse Chisholm
Jesse Chisholm

Reputation: 4026

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.

Upvotes: 7

MBulava
MBulava

Reputation: 521

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

Upvotes: 16

criticalfix
criticalfix

Reputation: 2870

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.

Upvotes: 1

Related Questions