help with an update using a subquery 1

[daglugub37]

I need help with a update using a subquery. I either get syntax errors or unwanted updates. SQL Help was not much help for this scenario.

Table = Games
Columns and sample record

Hteam Hscore Ateam Ascore weekid hteamid ateamid
49ers 17 Chargers 24 7 null null

Basically this is a work table which is populated by a feed. Since the feed only inputs team names I would like to get these records into production normalized version with just the teamids. The plan is to update the hteamid then the ateamid and finally insert into the prod table excluding hteam and ateam fieldss.

Problem is I can not figure out the update statement. Here is the select that gives me the hteamid's

select t.teamid
from games g
join teams t
on g.hteame = t.tname

But how do I get this recordset into Games Hteamid?

Here is the best I could come up with which inserted incorrect teamid's

update games
set hteamid = t.teamid
from teams t
where t.teamid in(select t.teamid
from games g
join teams t
on g.hteame = t.tname)

 

[amrita418]

Try this:

update 	g
set 	g.hteamid = t.teamid
from 	games g inner join  teams t
	on (g.hteam = t.tname)
-- where clause if you want to update only some records

Regards,
AA

 

[daglugub37]

perfect, thanks AA

I need to remember select does not always have to be included in every statement I issue.

 

[amrita418]

btw, you can update the hteamid and ateamid in one stmt.

you can try something like this:

update   g
set      g.hteamid = t.teamid,
         g.ateamid = t1.teamid
from     games g inner join teams t
         on (g.hteam = t.tname)
         inner join teams t1
         on (g.ateam = t1.tname)

-- assuming you have an entry for each hteam, ateam in team table.

Regards,
AA