SQL Server 2005 Outer Join Problem In Stored Procedure

Question

Good Afternoon All,

I have two tables in my SQL Server 2005 DB, Main and MSDRGWEIGHTS. I want to create a stored procedure that updates Main.RelWeight with the appropriate value from MSDRGWEIGHTS. I have written the following code as part of the stored procedure:

UPDATE MAIN 
left outer join MSDRGWEIGHTS AS W ON MAIN.MSDRG=W.MSDRG
SET M.RELWEIGHT =
CASE
WHEN M.DISCHARGEDATE BETWEEN 20071001 AND 20080930 THEN W.WEIGHTSOCT07
WHEN M.DISCHARGEDATE BETWEEN 20081001 AND 20090930 THEN W.WEIGHTSOCT08
END
END
GO

When I execute this code, I receive error message stating incorrect syntax near keyword left. Can anyone suggest the proper way to update RELWEIGHT using a stored procedure? Or, is this outside the scope of a sproc?

Thanks, Sid

Answer 1

    UPDATE 
          m 
     SET m.RELWEIGHT= CASE
                        WHEN M.DISCHARGEDATE BETWEEN 20071001 AND 20080930 THEN W.WEIGHTSOCT07WHEN   
                       WHEN M.DISCHARGEDATE BETWEEN 20081001 AND 20090930 THEN W.WEIGHTSOCT08
    END 
    FROM 
          MAIN m 
   INNER JOIN MSDRGWEIGHTS mw 
   ON 
   mw.MSDRG = m.MSDRG