SQLTeam.com | Weblogs | Forums

MS Access Query to SQL?


#1

Hi

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

Thanks

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];

MS SQL

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

#2

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.


#3

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

Phil