搞砸了 SQL 数据 - 在更新语句中选择 [英] Messed up SQL data - Select within update statement

问题描述

我不小心运行了两次查询，数据库中的所有点都搞砸了(3000 条记录).

I accidentally ran a query twice and all the points in my database have messed up (3000 records).

每个结果的前 4 名有固定结果，但计算最后和第 5 名之间的分数(最后为 100 分).

Top 4 for each result has fixed results but points between last and 5th are calculated (Last is 100pts).

点击此处了解更多信息

我基本上需要将此语句转换为 SQL:

I essentially need this statement converting to SQL:

点数 = 100 + ((100/(NumberOfResults - 4)) * (NumberOfResults - PositionOfResult))

Points = 100 + ((100 / (NumberOfResults - 4)) * (NumberOfResults - PositionOfResult))

我怎样才能得到一个选择语句来分别引用 Select 和 Update 表.这不起作用:

How can I get a select statement to refer to the Select and the Update table separately. This doesn't work:

UPDATE    Results R1
SET       R1.Points = 100 + ((100/((SELECT TOP 1 R2.Position FROM Results R2 WHERE R2.Date = R1.Date AND R2.ContestID = R1.ContestID ORDER BY R2.Position DESC)-4) * ((SELECT TOP 1 R2.Position FROM Results R2 WHERE R2.Date = R1.Date AND R2.ContestID = R1.ContestID ORDER BY R2.Position DESC)-R1.Position)))
WHERE     R1.ContestID > 11
AND       R1.Position > 4
AND       R1.Position < (SELECT TOP 1 R2.Position FROM Results R2 WHERE R2.Date = R1.Date AND R2.ContestID = R1.ContestID ORDER BY R2.Position DESC)

推荐答案

只需要使用FROM"从正确的表中显示更新

Just needed the update to show from the correct table using "FROM"

UPDATE    R1
SET       R1.Points = 100 + ((100/((SELECT TOP 1 R2.Position FROM Results R2 WHERE R2.Date = R1.Date AND R2.ContestID = R1.ContestID ORDER BY R2.Position DESC)-4) * ((SELECT TOP 1 R2.Position FROM Results R2 WHERE R2.Date = R1.Date AND R2.ContestID = R1.ContestID ORDER BY R2.Position DESC)-R1.Position)))
From      Results R1
WHERE     R1.ContestID > 11
AND       R1.Position > 4
AND       R1.Position < (SELECT TOP 1 R2.Position FROM Results R2 WHERE R2.Date = R1.Date AND R2.ContestID = R1.ContestID ORDER BY R2.Position DESC)

这篇关于搞砸了 SQL 数据 - 在更新语句中选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！