SQL query, update from, multiple inner join

Question

lets say I have 3 tables in my SQL database:

AspNetUserRoles - hold records that links user with role AspNetUsers - holds users AspNetRoles - holds roles

I wanted to update role assigned to user whose email is: some@email.com

I created this query and it works:

-- Update tester account role
Update  AspNetUserRoles
Set RoleId = 4
From AspNetUsers as b Inner Join AspNetUserRoles as a
    On a.UserId = b.Id Where b.Email = 'some@email.com'

Now, how to edit this query, instead of supplying RoleId (in this case 4), retrieve specific role ID from its table by specifying its name? AspNetRoles table has field 'name', I tried with another inner join but failed. Any help would be appreciated, thank you.

I am using this query for automation testing, email is known for me (it is email from account set up for automation tester, thus it never change) thanks — Matthewek, Apr 08 '19 at 11:50

score 2 · Accepted Answer · answered Apr 08 '19 at 11:44

2

In your UPDATE query I've removed the JOIN because you have this information in your mail table.

So I've add the subquery to update your RoleId

Try this:

UPDATE AspNetUserRoles
SET RoleId = (SELECT c.id FROM AspNetRoles c WHERE c.name = 'MyRoleName')
FROM AspNetUsers as b 
WHERE b.Email = 'some@email.com'
AND AspNetUserRoles.UserId = b.Id

answered Apr 08 '19 at 11:44

Joe Taras

15,166
7
42
55

It works, I will accept answer within few minutes that remained, thank you. – Matthewek Apr 08 '19 at 11:46
1

@Matthewek: Have a nice day ;) – Joe Taras Apr 08 '19 at 11:47

score 1 · Answer 2 · answered Apr 08 '19 at 11:45

You can write a query to fetch the id of the role depending of the name

SELECT r.id
FROM AspNetRoles r
WHERE r.name = 'YourRoleName'
LIMIT 1

And use this query as subquery in your main :

Update  AspNetUserRoles
Set RoleId = 
(
    SELECT r.id
    FROM AspNetRoles r
    WHERE r.name = 'YourRoleName'
    LIMIT 1
)
From AspNetUsers as b Inner Join AspNetUserRoles as a
    On a.UserId = b.Id Where b.Email = 'some@email.com'

Gordon Linoff · Answer 3 · 2019-04-08T11:50:19.950

1

You can use a JOIN like this:

update  aur
    set RoleId = 4
from AspNetUsers au join
     AspNetUserRoles  ur
     on aur.UserId = au.Id join
     AspNetRoles r
     on ur.RoleId = r.roleId
where au.Email = 'some@email.com' and
      r.Name = ?;

This seems strange, though. ApsNetUserRoles sounds like a junction table. I would expect you to add a role by inserting a role into this table.

edited Apr 08 '19 at 11:50

answered Apr 08 '19 at 11:49

Gordon Linoff

1,242,037
58
646
786

Gordon : *"how to edit this query, instead of supplying RoleId (in this case 4), retrieve specific role ID from its table by specifying its name?"* – Cid Apr 08 '19 at 11:50

score 0 · Answer 4 · edited Apr 08 '19 at 12:31

0

Try this:

  Update AspNetUserRoles
    Set AspNetUserRoles.RoleId = a.RoleId
    From AspNetUsers as b 
    Inner Join AspNetUserRoles as a
        On a.UserId = b.Id 
    Where b.Email = 'some@email.com'
      and  a.name = 'name'

edited Apr 08 '19 at 12:31

Daniel Quinlan

2,639
1
20
23

answered Apr 08 '19 at 11:53

Rima

1,447
1
6
12

SQL query, update from, multiple inner join

4 Answers4