0

I'm trying to update my riders total ucipoints by following an example, with some small modifications, but I just get an error from it.

example

UPDATE P 
SET extrasPrice = t.TotalPrice
FROM BookingPitches AS P INNER JOIN
 (
  SELECT
    PitchID,
    SUM(Price) TotalPrice
  FROM
     BookingPitchExtras
  GROUP BY PitchID
  ) t
ON t.PitchID = p.ID

I got the code from this answer: SQL Update to the SUM of its joined values

My code looks like this:

 UPDATE P
SET ucipoeng = t.TotalPoints
FROM rytterlagsesong AS P INNER JOIN
 (
  SELECT
    rytterid,
    SUM(poeng) AS TotalPoints
  FROM
     t_ucipoeng
WHERE year(dato)='2016'
  GROUP BY rytterid
  ) t
ON t.rytterid = P.rytterid AND t.sesong='2016'

I get the error:

1064 - 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 'FROM rytterlagsesong AS P INNER JOIN ( SELECT rytterid, SUM(ucip' at line 3

Can someone help me find the error?

DB Structure: rytterlagsesong: rytterid - ucipoeng - sesong t_ucipoeng: rytterid - dato - poeng

So I want to sum the points (poeng) of all races in 2016 (dato=date) for a rider And update that riders totalpoint (ucipoeng) for this season (sesong)

Community
  • 1
  • 1
FingeNB
  • 223
  • 2
  • 10

1 Answers1

1

The update / join syntax is different per database. For mysql, use this:

UPDATE rytterlagsesong r
    INNER JOIN (
        SELECT rytterid, SUM(poeng) AS TotalPoints
        FROM t_ucipoeng
        WHERE year(dato)='2016'
        GROUP BY rytterid
    ) t ON t.rytterid = r.rytterid AND t.sesong='2016'
SET r.ucipoeng = t.TotalPoints
sgeddes
  • 62,311
  • 6
  • 61
  • 83
  • worked perfect! Found one small typo, that was my fault, t.sesong should be r.sesong (its wrong in my original post too) – FingeNB Feb 29 '16 at 14:53