asked on

Update each row in a select statement

I need to create a job to clean up some data every night. I need to do a select statement (that joins multiple tables) and then loop through each record and do an update to one of the related tables. How can I do this?

Guy Hengel [angelIII / a3]

why not update all the rows at once, based on the select statement?

centdevs

ASKER

That's fine too, I didn't know I could.

chapmandew

like this

update t3
set field = t4.field
from
table1 t1
join table2 t2 on t1.field = t2.field
join tabl3 t3 on t2.field2 = t3.field2
join table4 t4 on t3.field3 = t4.field3

centdevs

ASKER

Having a bit of trouble getting my where clause in there. Here is the select clause that shows the rows that would need to be updated:

SELECT p.PROF_UserID, p.PROF_acctno, ur.RoleID, r.RoleName, m.MemberType, m.MemberStatus
FROM dev..profile p (NOLOCK)
INNER JOIN mastermember..vwMember m
ON m.Member_acctno = p.PROF_acctno
INNER JOIN dev..aspnet_UsersInRoles ur
ON ur.UserID = p.PROF_UserID
INNER JOIN dev..aspnet_Roles r
ON r.RoleID = ur.RoleID
WHERE m.MemberStatus IN ('103', '109')
AND ur.RoleID IN ('21a3ea4b-3bc0-4308-bb3f-03e70e3b62f5',
'1141de1a-ddf8-40a1-9fc5-d0eb0ee2c580',
'936f46a7-a0b7-4162-8353-ed6c2391b5b2',
'204035cf-aa6a-47f3-af47-b834b73aed07')

chapmandew

what do you want to update?

centdevs

ASKER

I need to update dev..aspnet_UsersInRoles.RoleID

chapmandew

to what value(or field)?

centdevs

ASKER

ur.RoleID = '0029df3c-6163-40ac-b254-7db6bad49780'

ASKER CERTIFIED SOLUTION

chapmandew