How to insert into a SQL table column using SELECT query?

I have tables A, B, and C.

I want to insert UserRoleName values in table C, into UserRoleName column in table A for the corresponding UserId. UserId in Table A, is a foreign key from Table B. UserRoleId is a foreign key in table B from table C. How can I do that?

I tried using this query,

insert into A (UserRoleName)
(
select C.UserRoleName
from C
inner join B on B.UserRoleId = C.UserRoleId
inner join A on A.UserId  = B.UserId
)

But this is giving:

Cannot insert the value NULL into column ‘Z

which is a non-nullable in column A. I didn’t specify that column in picture of the table A though. Can you help me figure out what I am doing wrong?

Answer

You need to use update as follows:

Update a
   Set a.userrolename = c.rolename
 From b join c on b.userroleid = b.userroleid and b.userid = a.userid
Where a.userrolename is null

Or using standard sql (subquery) as follows:

Update a
  Set a.userrolename = 
      (Select c.userrolename
         From b join c on b.userroleid = c.userroleid
        Where b.userid = a.userid)
 Where a.userrolename is null;