SQLTeam.com | Weblogs | Forums

MS Access Query to SQL?


I have an query from MS Access which I have converted to MS SQL (2008). Can you tell me if this is the best option to convert the query and also have I actually converted it correctly? It seems to work as expected, but just need to check my logic and conversion.

The desired outcome is to set the [Risks and Issues].PlanID column to the @unallocated value for any rows that are linked to the Plan table with column values of Deleted = True and WorkstreamID = @ID

Hope this makes sense


MS Access

UPDATE (Workstreams INNER JOIN Plans ON Workstreams.ID = Plans.WorkstreamID) INNER JOIN [Risks and Issues] ON Plans.ID = [Risks and Issues].PlanID SET [Risks and Issues].PlanID = [@UNALLOCATED]
WHERE Plans.Deleted=True And Plans.WorkstreamID=[@ID];


Update [Risks and Issues]
	Set [Risks and Issues].PlanID = @ID
	Workstreams INNER JOIN Plans ON Workstreams.ID = Plans.WorkstreamID INNER JOIN [Risks and Issues] ON Plans.ID = [Risks and Issues].PlanID 
	Plans.Deleted=1 And Plans.WorkstreamID = @WorkstreamID

Yes looks good. You may want to check if the join conditions are indexed, and possibly add indexes if they are not. Also consider adding the Deleted column to the WorkStreamID index.

1 Like

Thanks Tara - good point about the indexes; not considered that so will update accordingly