[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Update list of rows from subquery

Posted on 2011-05-09
Medium Priority
Last Modified: 2012-05-11
I'll start by explaining my tables:

aspnet_Users:             UserID, UserName . . .
aspnet_Roles:              RoleID, RoleName . . .
aspnet_UsersInRoles:  UserID, RoleID

I want a query that assigns a role to all users that currently do not have a role.

One of my attempts:

INSERT INTO aspnet_UsersInRoles 
(SELECT UserId FROM aspnet_Users WHERE UserId NOT IN (SELECT UserId FROM aspnet_UsersInRoles))

Open in new window

Question by:echobridge
  • 2

Author Comment

ID: 35724863
Please note that if a user does not have a role, they will not be in the UsersInRoles table.
LVL 41

Accepted Solution

Sharath earned 2000 total points
ID: 35725064
try this.
INSERT INTO aspnet_UsersInRoles(UserID, RoleID)
SELECT UserId,'DEA47E12-1BFA-45C0-B994-F35025833922' 
  FROM aspnet_Users t1
 WHERE NOT EXISTS (SELECT 1 FROM aspnet_UsersInRoles t2 WHERE t1.UserId = t2.UserId)

Open in new window

LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35728210
insert or update?

if it's update, please read this article: http://www.experts-exchange.com/A_1517.html

Author Closing Comment

ID: 35729017
WOO! Worked perfect!!

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question