CREATE TABLE record_points (
Name varchar(16) COLLATE utf8mb4_bin NOT NULL,
Points int(11) DEFAULT '0',
UNIQUE KEY Name (Name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
did you let the server create the tables or did you create them by hand? Is records_maps filled or not?
SHOW TABLES;?
SELECT NULL FROM record_points; (edited)FROM record_race;
SELECT NULL FROM record_race; but w/e this works aswell
INSERT IGNORE INTO (Name, Points)
(SELECT Name, Points
FROM record_race t1
INNER JOIN record_maps t2 ON t1.Map = t2.Map;
GROUP BY Name, t1.Map)
ON DUPLICATE KEY UPDATE Name=VALUES(Name), Points=Points+VALUES(Points);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'GROUP BY Name, t1.Map)
ON DUPLICATE KEY UPDATE Name=VALUES(Name), Points=Poi' at line 1INSERT INTO record_points (Name, Points)
SELECT Name, sum(pt) FROM (SELECT (m.Points) AS pt, r.Name
FROM record_race r
LEFT JOIN record_maps m
ON r.Map = m.Map
GROUP BY r.Map, r.Name) a
GROUP BY a.Name
HAVING sum(pt) > 0;
INSERT IGNORE INTO record_points
SELECT Name,
Sum(pt)
FROM (SELECT ( m.points ) AS pt,
r.NAME
FROM record_race r
LEFT JOIN record_maps m
ON r.map = m.map
GROUP BY r.Map,
r.Name) a
GROUP BY a.Name
HAVING Sum(pt) > 0 should work
