In den Beispielen ist die Liga 1 der Saison 2011 gewählt, also die aktuelle 1. Bundesliga. Für andere Ligen ist zumindest die Liganummer jeweils zu verändern.
Wenn man nur den Subselect, also REPLACE ... weglässt und nur den Teil nach SELECT verwendet oder UPDATE weglässt und nur den Select in Klammern verwendet, kann man vorher überprüfen, was eventuell korrigiert wird.
1. Nur bei Ligen mit "Tippabgabe bis zum jeweiligen Spielbeginn".
Korrektur des Abgabetermins und Status bei den Spieltagen:
Code: Alles auswählen
# Erster Spielbeginn als Abgabetermin bei Tippabgabe bis zum Spielbeginn
REPLACE INTO phpbb_footb_matchdays (season, league, matchday, delivery_date, matchday_name, matches)
SELECT m.season, m.league, m.matchday, min(m.match_datetime) as delivery_date, md.matchday_name, md.matches
FROM phpbb_footb_matches as m
JOIN phpbb_footb_leagues AS l ON (l.season = m.season AND l.league = m.league)
JOIN phpbb_footb_matchdays AS md ON (md.season = m.season AND md.league = m.league AND md.matchday = m.matchday)
WHERE l.bet_in_time = 1 AND m.status=0
GROUP BY m.season, m.league, m.matchdayKorrektur eines Abgabetermins, der nach dem ersten Spielbeginn liegt, auf gerade diesen:
Code: Alles auswählen
# Erster Spielbeginn als Abgabetermin bei festem Abgabetermin
REPLACE INTO phpbb_footb_matchdays (season, league, matchday, status, delivery_date, delivery_date_2, delivery_date_3, matchday_name, matches)
SELECT md.season
, md.league
, md.matchday
, md.status
, min_delivery_date as delivery_date
, md.delivery_date_2
, md.delivery_date_3
, md.matchday_name
, md.matches
FROM phpbb_footb_matchdays AS md
INNER JOIN (SELECT season, league, matchday, min(match_datetime) as min_delivery_date
FROM phpbb_footb_matches
WHERE season = 2011 AND league = 1 AND status = 0
GROUP BY season, league, matchday) as agg
where md.season = agg.season
AND md.league = agg.league
AND md.matchday = agg.matchday
AND md.delivery_date > agg.min_delivery_date
# Zweiter Spielbeginn als Abgabetermin bei festem Abgabetermin
REPLACE INTO phpbb_footb_matchdays (season, league, matchday, status, delivery_date, delivery_date_2, delivery_date_3, matchday_name, matches)
SELECT md.season
, md.league
, md.matchday
, md.status
, md.delivery_date
, min_delivery_date as delivery_date_2
, md.delivery_date_3
, md.matchday_name
, md.matches
FROM phpbb_footb_matchdays AS md
INNER JOIN (SELECT season, league, matchday, min(match_datetime) as min_delivery_date
FROM phpbb_footb_matches
WHERE season = 2011 AND league = 1 AND status = -1
GROUP BY season, league, matchday) as agg
where md.season = agg.season
AND md.league = agg.league
AND md.matchday = agg.matchday
AND md.delivery_date_2 > agg.min_delivery_date
# Zweiter Spielbeginn als Abgabetermin bei festem Abgabetermin
REPLACE INTO phpbb_footb_matchdays (season, league, matchday, status, delivery_date, delivery_date_2, delivery_date_3, matchday_name, matches)
SELECT md.season
, md.league
, md.matchday
, md.status
, md.delivery_date
, md.delivery_date_2
, min_delivery_date as delivery_date_3
, md.matchday_name
, md.matches
FROM phpbb_footb_matchdays AS md
INNER JOIN (SELECT season, league, matchday, min(match_datetime) as min_delivery_date
FROM phpbb_footb_matches
WHERE season = 2011 AND league = 1 AND status = -2
GROUP BY season, league, matchday) as agg
where md.season = agg.season
AND md.league = agg.league
AND md.matchday = agg.matchday
AND md.delivery_date_3 > agg.min_delivery_date
Korrektur des Spieltags Status:
Code: Alles auswählen
# Korrekten Status im Spieltag setzen
UPDATE phpbb_footb_matchdays AS target
INNER JOIN
(
SELECT md.season
, md.league
, md.matchday
, IF( md.delivery_date > now(),
0,
IF(ISNULL(min(e.extra_status)),
IF(min(m.status) = 1 AND max(m.status) > 1,
2,
GREATEST(min(m.status), 0)),
IF(LEAST(min(m.status), min(e.extra_status)) = 1 AND GREATEST(max(m.status), max(e.extra_status)) > 1,
2,
GREATEST(LEAST(min(m.status), min(e.extra_status)), 0)))) As new_status
FROM phpbb_footb_matchdays AS md
LEFT JOIN phpbb_footb_matches AS m ON (m.season = md.season AND m.league = md.league AND m.matchday = md.matchday)
LEFT JOIN phpbb_footb_extra AS e ON (e.season = md.season AND e.league = md.league AND e.matchday_eval = md.matchday)
WHERE md.season = 2011 AND md.league = 1
GROUP BY md.season, md.league, md.matchday) AS source
ON target.season = source.season AND target.league = source.league AND target.matchday = source.matchday
SET target.status = source.new_status