Update query help... - 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 query help...

Author

Topic

danielc
Starting Member

49 Posts

Posted - 2009-08-04 : 13:29:38

hello I need help with the following update query:

UPDATE SSE1 SET SSE1.NetworkLogonId = SSE1.NetworkLogonId, SSE1.NetworkPassword = SSE1.NetworkPassword
FROM temp AS SSE1 INNER JOIN temp AS SSE2 ON SSE1.StudentPersonalRefID = SSE2.StudentPersonalRefID
WHERE SSE1.IsPertinent = 0
AND (SSE1.NetworkLogonId IS NOT NULL OR SSE1.NetworkLogonId <> '')
AND (SSE1.NetworkPassword IS NOT NULL OR SSE1.NetworkPassword <> '')
GROUP BY StudentPersonalRefID

create table temp (
refid int primary key,
studentpersonalrefid int,
networklogonid int,
networkpassword int)

insert into temp
select 1, 1, 1, 1
union
select 2, 1, 0, 0
union
select 3, 3, 2, 2
union
select 4, 4, 4, 4
union
select 5, 4, 0, 0

what I need is to update networklogonid and networkpassword when studentpersonalrefid is the same. The GROUP BY works on a SELECT statement but won't work on a UPDATE statement.

Thanks,

Daniel

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-08-04 : 14:01:07

UPDATE SSE1
SET SSE1.NetworkLogonId = SSE2.NetworkLogonId
, SSE1.NetworkPassword = SSE2.NetworkPassword
FROM #temp AS SSE1
INNER JOIN #temp AS SSE2 ON
SSE1.StudentPersonalRefID = SSE2.StudentPersonalRefID
WHERE
-- SSE1.IsPertinent = 0
(SSE1.NetworkLogonId IS NOT NULL OR SSE1.NetworkLogonId <> '')
AND (SSE1.NetworkPassword IS NOT NULL OR SSE1.NetworkPassword <> '')
~~GROUP BY StudentPersonalRefID~~

Jim

danielc
Starting Member

49 Posts

Posted - 2009-08-04 : 14:30:57

Thank you... That worked just fine.

Daniel

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources