1

Why gives this code next error on the last line?

Invalid column name 'rId'.

;WITH x AS 
(
    SELECT Id as rId
    FROM Roles
    WHERE Minimum >= (select punten 
                      from Users 
                      where id = 'abc')
)
INSERT INTO UserRoles (UserId, RoleId) 
VALUES ('abc', rId);

I use MS-SQL server.

1 Answer 1

3

I've found it! The code must be this:

;WITH rs AS 
(
    SELECT Id 
    FROM AspNetRoles
    WHERE Minimum >= (select punten from AspNetUsers where id = 'abc')
)
INSERT INTO AspNetUserRoles (UserId, RoleId)
SELECT 'abc', rs.id
FROM rs;
1
  • 3
    Or just INSERT INTO AspNetUserRoles (UserId, RoleId) SELECT 'abc', Id FROM AspNetRoles WHERE Minimum >= (select punten from AspNetUsers where id = 'abc');. On a different note, please consider putting semicolons at the end of each statement rather than at the beginning – that's where they are supposed to be.
    – Andriy M
    Nov 27, 2015 at 12:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.