如何将“选择”查询更改为“更新”查询 [英] How to change a Select query to an Update query

问题描述

以下SELECT查询从表中选择正确的记录子集，并在字段X中计算正确的新RANK值。 如何将其更改为UPDATE查询以及使用新值更新RANK字段？  SELECT
查询的结果显示在SQL

The following SELECT query selects the correct subset of records from a table and calculates the correct new RANK value in the field X.  How do I change it to an UPDATE query to  update the RANK field with the new value?  Results of SELECT query are shown below the SQL

下面。我尝试在设计屏幕中更改查询类型，但这只是创建了语法错误。

I have tried just changing the query type in the design screen but that just created syntax errors.

SELECT T1.TriAthID，T1.SubEvent，T1.JudgeID，T1.Score，T1.Rank，（选择Count（*）来自tblRecapA T2

其中T2.JudgeID = T1.JudgeID和

  T2.TriAthID = T1.TriAthID和

  T2.ADSortKey = T1.ADSortKey和

  T2.SubEvent = T1.SubEvent和$
T2.Score> = T1.Score）AS X

FROM tblRecapA AS T1

WHERE（（（T1.TriAthID）= 2493）AND（（T1.SubEvent）= 1）AND（（T1.JudgeID）= 838））;

SELECT T1.TriAthID, T1.SubEvent, T1.JudgeID, T1.Score, T1.Rank, (Select Count(*) From tblRecapA T2
Where T2.JudgeID=T1.JudgeID And
 T2.TriAthID =T1.TriAthID And
 T2.ADSortKey =T1.ADSortKey And
 T2.SubEvent =T1.SubEvent And
T2.Score>=T1.Score ) AS X
FROM tblRecapA AS T1
WHERE (((T1.TriAthID)=2493) AND ((T1.SubEvent)=1) AND ((T1.JudgeID)=838));

选择查询结果：

推荐答案

史蒂夫，

您可以尝试这样的事情（确保先备份）：

You might try something like (make sure you have a backup first) this:

UPDATE tblRecapA T1 SET T1.Rank =（SELECT Count（*）FROM tblRecapA T2

UPDATE tblRecapA T1 SET T1.Rank = (SELECT Count(*) FROM tblRecapA T2

WHERE T2.JudgeID = T1.JudgeID AND T2.TriAthID = T1。 TriAthID

WHERE T2.JudgeID=T1.JudgeID AND T2.TriAthID=T1.TriAthID

AND T2.ADSortKey = T1.ADSortKey AND T2.SubEvent = T1.SubEvent AND T2.Score = T1.Score）

AND T2.ADSortKey=T1.ADSortKey AND T2.SubEvent=T1.SubEvent AND T2.Score=T1.Score)

WHERE T1 .TriAthID = 24 93 AND T1.SubEvent = 1 AND T1.JudgeID = 838

WHERE T1.TriAthID=2493 AND T1.SubEvent=1 AND T1.JudgeID=838

希望它有所帮助......

Hope it helps...

这篇关于如何将“选择”查询更改为“更新”查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！