I was composing an update query by hand today in Management Studio. I thought it was a simple SQL statement that simply joined my Control table to a Query table using two fields to link the tables. My original SQL looked like this:
from control join query q
Management Studio changed the SQL to be this:
SET PolicyProcedure = Control.PolicyProcedure
FROM Control INNER JOIN
Query AS q ON Control.ActivityFK = q.activityfk
AND Control.SystemFK = q.systemfk
Notice that the SET now pulls the PolicyProcedure field from Control instead of my aliased Query (Q). That's not what I ordered!
I finally got the desired results (sans any changing of aliases by Management Studio) using this SQL:
SET PolicyProcedure = x.policyprocedure
FROM (SELECT c.ControlID, q.policyprocedure
FROM Control AS c INNER JOIN
Query AS q ON c.SystemFK = q.systemfk
AND c.ActivityFK = q.activityfk) AS x
Control ON x.ControlID = Control.ControlID
Is this a bug, or am I a moron who needs to study up on SQL? Maybe I should have used a CROSS JOIN and WHERE clause in my original update query instead of the INNER JOIN?