Update Table with select - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Update Table with select

Author

Topic

jjmusicpro
Yak Posting Veteran

79 Posts

Posted - 2010-05-19 : 12:40:52

I cant seem to get this update correct.

I have the table stucture like below. It should look at ad_username in the master_table, check that user id in both sis_students_groups and ad_students_groups tables, to see if they all match, or if one is missing from either table. If the items do not match, then group_result should = 1, if they do match group_result should = 0.

My master_table (pushed in from full outer join)
structre like
ad_username - username - join result - group_result

data example

joe1 - joe1 - 0 - 0
joe2 - joe2 - 0 - 0
joe4 - NULL - 1 - 0
NULL - joe5 - 2 - 0

table name - sis_students_groups
id - student_id - student_group
data:
1 - joe1 - group 1
2 - joe1 - group 2
3 - joe2 - group 10
4 - joe1 - group 1

table name - ad_students_groups
id - student_id - student_group

data:
1 - joe1 - group 1
2 - joe1 - group 2
3 - joe2 - group 8
4 - joe1 - group 1

UPDATE mt
SET mt.group_result=m.result
FROM master_table mt
INNER JOIN (
SELECT ad_username,CASE WHEN SUM(CASE WHEN Stat=1 THEN 1 ELSE 0 END) >0 THEN 1 ELSE 0 END AS result
FROM
(
SELECT student_id,student_group,
CASE WHEN COUNT(DISTINCT cat) = 2 THEN 0 ELSE 1 END AS Stat
FROM
(SELECT student_id,student_group,'A' as cat
FROM ad_students_groups
UNION
SELECT student_id,student_group,'B' as cat
FROM sis_students_groups
)t
GROUP BY student_id,student_group
)r
GROUP BY student_id
)m
ON m.student_id = mt.ad_username

Here is the error:

Msg 207, Level 16, State 1, Line 5
Invalid column name 'ad_username'.
Msg 207, Level 16, State 1, Line 21
Invalid column name 'student_id'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-19 : 12:57:49

[code]
UPDATE mt
SET mt.group_result=m.result
FROM master_table mt
INNER JOIN (
SELECT ~~ad_username~~student_id,CASE WHEN SUM(CASE WHEN Stat=1 THEN 1 ELSE 0 END) >0 THEN 1 ELSE 0 END AS result
FROM
(
SELECT student_id,student_group,
CASE WHEN COUNT(DISTINCT cat) = 2 THEN 0 ELSE 1 END AS Stat
FROM
(SELECT student_id,student_group,'A' as cat
FROM ad_students_groups
UNION
SELECT student_id,student_group,'B' as cat
FROM sis_students_groups
)t
GROUP BY student_id,student_group
)r
GROUP BY student_id
)m
ON m.student_id = mt.ad_username
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jjmusicpro
Yak Posting Veteran

79 Posts

Posted - 2010-05-19 : 13:02:43

THANKS!!!!!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-19 : 13:05:47

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources