Friday, June 24, 2005

SQL Server 2005 Management Studio bug???

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:

update control
set policyprocedure=q.policyprocedure
from control join query q
on control.activityfk=q.activityfk
and control.systemfk=q.systemfk


Management Studio changed the SQL to be this:

UPDATE  Control
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:

UPDATE  Control
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
INNER JOIN
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?

Comments anyone?