quote:

---

Originally posted by hanbingl

UPDATE EResults SET ba_id = @baid
from EResults r inner join Employee e
on r.test_emp_id = e.emp_id
where r.test_emp_id = right(@ssn,6)
having count(e.emp_id) = 1

---

quote:

---

Originally posted by visakh16

UPDATE r 
SET r.ba_id = @baid
FROM EResults r 
INNER JOIN (SELECT COUNT(emp_id) AS EmpCount
            FROM Employee e
			WHERE emp_id=r.test_emp_id 
			AND   emp_id = right(@ssn,6))e
WHERE e.EmpCount = 1

---

quote:

---

Originally posted by Vinnie881

UPDATE a
SET a.ba_id = @baid
from
EResults a 
WHERE 
a.test_emp_id = right(@ssn, 6)
and 1 = (Select Count(*) from Employee e where  right(e.ssn,6) = right(@ssn,6)) 

---

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

---

quote:

---

Originally posted by lsulindy

quote:

---

Originally posted by visakh16

UPDATE r 
SET r.ba_id = @baid
FROM EResults r 
CROSS APPLY INNER JOIN (SELECT COUNT(emp_id) AS EmpCount
            FROM Employee e
			WHERE emp_id=r.test_emp_id 
			AND   emp_id = right(@ssn,6))e
WHERE e.EmpCount = 1

---

Thanks! I get the error "Incorrect syntax near the keyword WHERE" on the last line. Do I need and "ON" for the join? Here's a bit of background. We are transmitted test results for our employees. The only identifier we are sent is the last 6 digits of the employee's ssn. The ba_id is our unique employee number. When the case comes up that 2 of our employees have the same last 6 ssn digits, I do not assign a ba_id to the test result. Someone assigns those manually when that case comes up. So, if more than 1 employee exists in the Employee table with the same 6 digit ssn ending, then I do not want to assign a ba_id to the EResult record. This sp is used as part of a "fix" employee records button. It finds all of the EResults with the same last 6 digits as the employee's ssn and assigns them to the employee's ba_id. If there is more than one employee in our system with this employee's last 6 ssn digits, I do not want to assign all of the EResults to this employee's ba_id b/c then he/she will acquire all of the other employee's test results.

UPDATE r
SET r.ba_id = @ba_id
FROM EResults AS r
INNER JOIN (SELECT COUNT(right(ssn,6)) AS EmpCount
FROM Employee e
WHERE right(ssn,6) = right(@ssn,6)) as c
WHERE (c.EmpCount = 1 AND r.emp_test_id = right(@ssn,6))

---

quote:

---

Originally posted by Vinnie881

UPDATE a
SET a.ba_id = @baid
from
EResults a 
WHERE 
a.test_emp_id = right(@ssn, 6)
and 1 = (Select Count(*) from Employee e where  right(e.ssn,6) = right(@ssn,6)) 

---

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

---