Update game schedule - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Update game schedule

Author

Topic

oakton
Starting Member

3 Posts

Posted - 2015-04-01 : 10:36:38

Hi! I'm the amateur/volunteer webmaster for a non-profit youth sports organization. I'm hoping someone can give me some advice and steer me in the right direction with the problem with which I am currently wrestling: How to update a master game schedule with changes to the schedule throughout the season. BTW - I'm using MS SQL Server 2014 and I'm pretty much a newbe when it comes to SQL.

Many thanks!

Here's the situation:-
Before the start of each season, the various clubs/teams compile an initial game schedule using Excel. Once the master schedule is complete, it is sent to me. The initial master schedule is loaded into the database [MatchSchedule] from the Excel spreadsheet using the SQL Server Import/Export Wizard. This works well enough. However, during the season there are frequent small changes to dates & times, venues, etc. These changes are sent to me in Excel spreadsheets.

I need an efficient & ERROR FREE method of getting the new game details into the master schedule. I thought perhaps the easiest way to accomplish updates to the table MatchSchedule t1 was to use a temporary table MatchSchedule_Updates_temp t2 to hold the new game details (obtained from Excel spreadsheets) and then use UPDATE to update MatchSchedule with those games that have changed. The primary key is Match_Id.

Unfortunately, I keep getting the following error messages which I have been unable to correct!

Msg 102, Level 15, State 1, Line 4
Incorrect syntax near 't1'.
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'SET'.
Msg 156, Level 15, State 1, Line 41
Incorrect syntax near the keyword 'WHERE'.

Here is my code:-

USE [database]
GO

UPDATE dbo.MatchSchedule t1
SET (
t1.MatchDate
,t1.MatchTime
,t1.Venue
,t1.Field
,t1.Conference
,t1.Division
,t1.District
,t1.Gender
,t1.Home_Team
,t1.Away_Team
,t1.Referee
,t1.ACT_EMT
,t1.Notes
)
=

(SELECT
t2.MatchDate
,t2.MatchTime
,t2.Venue
,t2.Field
,t2.Conference
,t2.Division
,t2.District
,t2.Gender
,t2.Home_Team
,t2.Away_Team
,t2.Referee
,t2.ACT_EMT
,t2.Notes

FROM [dbo].[MatchSchedule_Updates_temp] t2

WHERE t2.[Match_Id] = t1.[Match_Id])

WHERE EXISTS (SELECT * FROM t2

WHERE t2.[Match_Id] = t1.[Match_Id])
;
GO

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2015-04-01 : 16:35:41

[code]UPDATE t1
SET
t1.MatchDate = t2.MatchDate
,t1.MatchTime = t2.MatchTime
,t1.Venue = t2.Venue
,t1.Field = t2.Field
,t1.Conference = t2.Conference
,t1.Division = t2.Division
,t1.District = t2.District
,t1.Gender = t2.Gender
,t1.Home_Team = t2.Home_Team
,t1.Away_Team = t2.Away_Team
,t1.Referee = t2.Referee
,t1.ACT_EMT = t2.ACT_EMT
,t1.Notes = t2.Notes
from
dbo.MatchSchedule t1
inner join
MatchSchedule_Updates_temp t2
on t2.[Match_Id] = t1.[Match_Id][/code]BTW, using an UPDATE does imply that there are not additions in your adjustments; new games, etc.
HTH

I would rather be the man who bought the Brooklyn Bridge than the one who sold it. -Will Rogers

oakton
Starting Member

3 Posts

Posted - 2015-04-01 : 17:54:43

Thank you! That worked perfectly. I have used joins before but did not think to use the inner join in this context.

You are correct! Once games are set for the season and finalized in the schedule no new games can be added - only the details.
Again, many thanks! This will save me a lot of time.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources