SQL Server - How to prompt an index in the Refresh command?

Question

I am trying to execute the following command to force the update to use a non-clustered index:

UPDATE Flights SET Airtime=5555678 WHERE Distance=10000 OPTION (TABLE HINT(Flights, INDEX (DistanceIndex))) 

... and this is a mistake:

Msg 8724, Level 16, State 1, Line 75
Unable to complete the request. The table function or OPENROWSET "Flights" cannot be specified in the TABLE HINT clause.

I cannot find clear advice on how the syntax should be properly formed inside the TABLE HINT statement.

Please, can someone tell me where I'm going wrong?

Answer 1

Try the following:

 UPDATE F SET F.Airtime=5555678 FROM Flights F WITH (INDEX (DistanceIndex)) WHERE F.Distance=10000 

or

 UPDATE Flights SET Airtime=5555678 FROM Flights -- this line is added to your initial query WHERE Distance=10000 OPTION (TABLE HINT(Flights, INDEX (DistanceIndex))) 

Answer 2

@ i-one answer is the correct and recommended way.

You can also use

 UPDATE F SET Airtime = 5555678 FROM Flights F WHERE Distance = 10000 OPTION (TABLE HINT(F, INDEX (DistanceIndex))) 

But the documentation says

We recommend using the INDEX, FORCESCAN, or FORCESEEK table index as a prompt only in the context of the planning guide.