当前位置: 动力学知识库 > 问答 > 编程问答 >

mysql - SQL: SUM and Update query error,

问题描述:

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)

网友答案:

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
分享给朋友:
您可能感兴趣的文章:
随机阅读: