2 inner joins in an update query?
-
i have 2 tables as follows:
tblSchedule
GameID
date
hometeamID
awayTeamID
homeScore
AwayScore
tblTeams
id
TeamName
now i woudl like to perform an update query that will update the homescore and awayscore in tblSchedule. i woudl liek to identify the games by gamedateTime, HomeTeam Name, and AwayTeam Name.
so... i would need to have an UPDATE query with 2 inner joins and i am not sure how to accomplish it. here is what i tried (it didnt work)
UPDATE tblSchedule
SET tblSchedule.HomeScore = 14,
Schedule.AwayScore = 17
INNER JOIN tblTeams AS HomeTeam
ON tblSchedule.HomeTeamID = HomeTeam.id
INNER JOIN tblTeams AS AwayTeam
ON tblSchedule.AwayTeamID = AwayTeam.id
WHERE tblSchedule.gamedatetime = '2005-12-20 14:00:00'
AND HomeTeam.FirstName = 'New England'
AND HomeTeam.LastName = 'Patriots
AND AwayTeam.FirstName = 'Green Bay'
AND AwayTeam.LastName = 'Packers
Note - obviously the data i am inserting above is just sample data i made up.
suggestions please?
-
I'll let you figure it out... but here's the main problem :
UPDATE Sched SET Sched.HomeScore = 14...
FROM dbo.TblSchedule Sched INNER JOIN ....
The rest seems ok. The only thing I'd do different is use the team ids instead of the names but that depends on how you load the data... and since I don't know your app I can't assume you are doing it wrong
. -
Hey thanks for the suggestion.
I tried adding the FROM clause in there and still i get an error:
" Syntax error or access violation: You have an error in your SQL syntax."
-
Can you post your new version pls?
-
my new version is:
UPDATE tblSchedule
SET tblSchedule.HomeScore = 14,
Schedule.AwayScore = 17
FROM tblSchedule
INNER JOIN tblTeams AS HomeTeam
ON tblSchedule.HomeTeamID = HomeTeam.id
INNER JOIN tblTeams AS AwayTeam
ON tblSchedule.AwayTeamID = AwayTeam.id
WHERE tblSchedule.gamedatetime = '2005-12-20 14:00:00'
AND HomeTeam.FirstName = 'New England'
AND HomeTeam.LastName = 'Patriots
AND AwayTeam.FirstName = 'Green Bay'
AND AwayTeam.LastName = 'Packers'
after that didnt work i thought i woudl try usign subqueries instead and still had no luck. here is how i tried to use subqueries:
UPDATE tblSchedule
SET tblSchedule.HomeScore = 14,
tblSchedule.AwayScore = 10
WHERE tblSchedule.gamedatetime = '2005-09-12 21:00:00'
AND tblSchedule.HomeTeamID = (select id from teams where firstname ='Atlanta' and lastname = 'Falcons')
AND tblSchedule.AwayTeamID = (select id from teams where firstname ='Philadelphia' and lastname = 'Eagles')
-
This compiles OK in my QA.. but I can't test without the tables so tell me how it goes.
UPDATE tblSchedule
SET tblSchedule.HomeScore = 14,
tblSchedule.AwayScore = 17
FROM tblSchedule
INNER JOIN tblTeams AS HomeTeam
ON tblSchedule.HomeTeamID = HomeTeam.id
INNER JOIN tblTeams AS AwayTeam
ON tblSchedule.AwayTeamID = AwayTeam.id
WHERE tblSchedule.gamedatetime = '2005-12-20 14:00:00'
AND HomeTeam.FirstName = 'New England'
AND HomeTeam.LastName = 'Patriots'
AND AwayTeam.FirstName = 'Green Bay'
AND AwayTeam.LastName = 'Packers'
-
i know this is a SQL server forum but for this particular project i happen to be using mySQL - that prob has something to do with it.
i was hoping the solution would be the same for sql server and mySQL.
-
There are not. Sorry but I can't help you anymore than I did. Somebody else will catch up with this sometime soon and figure it out.
-
well i appreciate your efforts thus far. as you said, hopefully someone else can chime in on this one. one last question - do you knwo of a good mySQL forum where i coudl post this question.
-
Nothing comes to mind. I have not used mysql in over 3 years (back in school actually) so I never really needed to find one.
Google maybe?
-
I do not know MySQL but you could try standard ANSI syntax:
UPDATE tblSchedule
SET HomeScore = 14
,AwayScore = 17
WHERE HomeTeamID = (
SELECT T1.[id]
FROM tblTeams T1
WHERE T1.FirstName = 'New England'
AND T1.LastName = 'Patriots'
)
AND AwayTeamID = (
SELECT T2.[id]
FROM tblTeams T2
WHERE T2.FirstName = 'Green Bay'
AND T2.LastName = 'Packers'
)
-
i think the problem may be that i am using mySQL 4.0 and i need to be on at least 4.1 for this to work. so now i have to figure out how to upgrade from 4.0 to 4.1 without harming my existing databases.
-
I have on occasion used up to 7 joined tables in an Update, and get problems when not aliasing the updated table. not sure why
UPDATE A
SET A.HomeScore = 14,
A.AwayScore = 17
FROM tblSchedule A
INNER JOIN tblTeams AS HomeTeam
ON A.HomeTeamID = HomeTeam.id
INNER JOIN tblTeams AS AwayTeam
ON A.AwayTeamID = AwayTeam.id
WHERE A.gamedatetime = '2005-12-20 14:00:00'
AND HomeTeam.FirstName = 'New England'
AND HomeTeam.LastName = 'Patriots'
AND AwayTeam.FirstName = 'Green Bay'
AND AwayTeam.LastName = 'Packers'
-
I find it interesting that you are constraining on HomeTeam, AwayTeam and gamedatetime. You only need one team name with gamedatetime, right? Because no one team can play two games simultaneously, true?
-
In theory...
what about if a team plays against hitself?
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply