Update query using select query result

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

SQL Server 2008 Forums

Transact-SQL (2008)

Update query using select query result

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2013-01-02 : 09:50:18

Is it possible for an update query to use the select query.


update T_EMPLOYEES set title_flag = (below select query TITLE_FLAG based on T_STG_LW_EMPLOYEE.Employee)

select DISTINCT T_STG_LW_EMPLOYEE.Employee,
 CASE T_STG_LW_AUMXVALUE.matrix_cat
	             WHEN  'DIR' then 'Y'
	             WHEN 'DIR2' then 'Y'
	             WHEN 'VP' then 'Y'
	             ELSE 'N'
              END as TITLE_FLAG
from T_STG_LW_EMPLOYEE
left outer join T_STG_LW_AUMXVALUE
on (ltrim(rtrim(T_STG_LW_EMPLOYEE.first_name)) + ' ' + ltrim(rtrim(T_STG_LW_EMPLOYEE.Last_name))) = 
T_STG_LW_AUMXVALUE.mx_value order by T_STG_LW_EMPLOYEE.employee

Join: T_EMPLOYEES.EMPLOYEE_NUMBER = T_STG_LW_EMPLOYEE.Employee

Thanks a lot for the helpful info.

Ifor
Aged Yak Warrior

700 Posts

Posted - 2013-01-02 : 10:10:41

[code]
UPDATE T_EMPLOYEES
SET title_flag =
CASE
WHEN EXISTS
(
SELECT 1
FROM T_STG_LW_EMPLOYEE E
WHERE E.Employee = T_EMPLOYEES.EMPLOYEE_NUMBER
AND EXISTS
(
SELECT 1
FROM T_STG_LW_AUMXVALUE A
WHERE A.mx_value = LTRIM(RTRIM(E.first_name)) + ' ' + LTRIM(RTRIM(E.Last_name))
AND A.matrix_cat IN ('DIR', 'DIR2', 'VP')
)
)
THEN 'Y'
ELSE 'N'
END;
[/code]

shan
Yak Posting Veteran

84 Posts

Posted - 2013-01-02 : 10:14:10

This will meet what you need.

update b
set title_flag = a.TITLE_FLAG
from T_EMPLOYEES b
inner join
(
select DISTINCT T_STG_LW_EMPLOYEE.Employee,
CASE T_STG_LW_AUMXVALUE.matrix_cat
WHEN 'DIR' then 'Y'
WHEN 'DIR2' then 'Y'
WHEN 'VP' then 'Y'
ELSE 'N'
END as TITLE_FLAG
from T_STG_LW_EMPLOYEE
left outer join T_STG_LW_AUMXVALUE
on (ltrim(rtrim(T_STG_LW_EMPLOYEE.first_name)) + ' ' + ltrim(rtrim(T_STG_LW_EMPLOYEE.Last_name))) =
T_STG_LW_AUMXVALUE.mx_value
) A
on b.EMPLOYEE_NUMBER = a.Employee

-Shan

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2013-01-02 : 13:04:58

Ifor, Shan, It worked.
Thank you very much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources