Updating a value where a table needs use a join

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Updating a value where a table needs use a join

Author

Topic

khovorka
Starting Member

8 Posts

Posted - 2014-03-27 : 12:20:23

Hello,

I have written the following SQL and it fails with the AS clause on line 1. Not sure how to correct and I appreciate any help in doing this correctly, see statement below:

UPDATE dbo.tbl_backup AS mfc
JOIN dbo.mypatient AS p
ON mfc.patientid = p.patient
SET mfc.ls_results = CASE
WHEN p.sex = 'Male' AND mfc.ls_strength >= 531.314 THEN mfc.ls_results = 'Excellent'
WHEN p.sex = 'Male' AND mfc.ls_strength >= 472 and mfc.ls_strength < 531.314 THEN mfc.ls_results = 'Above Average'
WHEN p.sex = 'Male' AND mfc.ls_strength >= 353 and mfc.ls_strength < 472 THEN mfc.ls_results = 'Average'
WHEN p.sex = 'Male' AND mfc.ls_strength >= 302 and mfc.ls_strength < 353 THEN mfc.ls_results = 'Below Average'
WHEN p.sex = 'Male' AND mfc.ls_strength < 302 THEN mfc.ls_results = 'Poor'
WHEN p.sex = 'Female' AND mfc.ls_strength >= 300 THEN mfc.ls_results = 'Excellent'
WHEN p.sex = 'Female' AND mfc.ls_strength >= 251 and mfc.ls_strength < 300 THEN mfc.ls_results = 'Above Average'
WHEN p.sex = 'Female' AND mfc.ls_strength >= 146 and mfc.ls_strength < 251 THEN mfc.ls_results = 'Average'
WHEN p.sex = 'Female' AND mfc.ls_strength >= 108 and mfc.ls_strength < 146 THEN mfc.ls_results = 'Below Average'
WHEN p.sex = 'Female' AND mfc.ls_strength < 108 THEN mfc.ls_results = 'Poor'
END
WHERE mfc.ls_results <> '' AND NOT(mfc.ls_results IS NULL)

Kim H.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-03-27 : 15:00:55

make a complete FROM clause - like this:


UPDATE mfc set
      mfc.blah bla = p.blah blah
from  dbo.tbl_backup AS mfc
JOIN  dbo.mypatient AS p
        ON p.patient = mfc.patientid

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources