August 10, 2010 at 7:20 pm
How would i make the following update statements into a single statement without missing any criteria..
update Employee
set jobcode=null where len(jobcode)<>9 or isnumeric(jobcode)=0
update Employee set jobcode = dept.jobcode
from Employee as e inner join Obbeaver.revenue.dbo.department dept
on e.ecode = dept.ecode where e.jobcode is null
August 10, 2010 at 7:57 pm
The two statements are not equivalent - combining them could eliminate those rows that don't match the second queries criteria, and those rows wouldn't be updated.
To make sure you can include all rows from Employee, you need to change the join in the second query to an outer join (LEFT JOIN), and then use a case statement to update the selected rows.
Update e
Set e.jobcode = Case When dept.jobcode Is Null And len(e.jobcode) <> 9 Then Null
When dept.jobcode Is Null And isnumeric(e.jobcode) = 0 Then Null
Else dept.jobcode
End
From dbo.Employee e
Left Join Obbeaver.revenue.dbo.department dept
On dept.ecode = e.ecode
Where e.jobcode Is Null
Or len(e.jobcode) <> 9
Or isnumeric(e.jobcode) = 0;
The above is not tested and may not be what you actually want - but it should give you an idea. You might be able to get rid of the case statement - if you know that all rows that don't have a related row in the department table have an invalid jobcode.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply