SQL Update throwing error at JOIN subquery

Question

I have an account table with a row for role_id. I have another table which is called organization group which holds the groups associated with an organization, this one has an ID and a name column. I have a third table called account_group which has two columns an account_id and a group_id to map both together.

Now I want to check the group of the account and based on the name of the group I will update the role id value of this account.

The query I managed to think of :

    UPDATE account 
SET account.role_id = (CASE WHEN organisation_group.name = "Social Worker" THEN 50
                            WHEN organisation_group.name = "Foster Carer" THEN 26
                       END)
    WHERE organisation_group.name in 
    (SELECT organisation_group.name FROM organisation_group 
INNER JOIN account_group ON organisation_group.id = account_group.group_id 
WHERE organisation_id = 11 GROUP BY organisation_group.name);

this is throwing the following error Unknown column 'organisation_group.name' in 'IN/ALL/ANY subquery' . I am not good at SQL but I need this update. any ideas how to do this operation.

50 and 26 are the role id I want to use.

Update 1 (Clearer Explaination)

I have an account table with columns [id,username,name,role_id,organisation_id].

I need to edit the account.role_id column. What I need to do is:

1. Check the accounts' group name.

The groups are in a table called organisation_group. This table has three columns [id,organisation_id,name].

To map the account with a group I have a table called account_group which has three columns [id. account_id , group_id].

2- Based on the group name I need to update the account.role_id to have the role id when (group name = role name).

The role is stored in a table called organisation_role which has columns [id,name,organisation_id].

Answer 1

Here's the technique I use. Instead of starting out trying to write a multi-table UPDATE statement, I start the process by writing a SELECT statement.

The goal of the SELECT statement is to return the rows I want to update (at least the primary key/unique key/identifier of the row), along with the column to be updated (the current values that are going to be replaced.) Along with that, I also return the new value that I want to assign the column.

I can tweak and play with that SELECT statement to get it to return exactly the rows I want. And I can verify the new values that are going to be assigned. And I can do all of that without possibly mucking up the database, and updating the wrong rows.

Once I have verified the SELECT statement, it's usually straightforward to convert that into an UPDATE statement. (In some cases, I will have to take the SELECT statement and wrap in parens and use it as an inline view.)

---

We can start with the table we want to update.

SELECT a.id
     , a.group_name
     , a.role_id
  FROM account a
 ORDER BY a.id

Add a join to account_group

SELECT a.id
     , a.group_name
     , a.role_id
     , g.group_id
  FROM account a
  JOIN account_group g
    ON g.account_id = a.id
 ORDER BY a.id

(Is that still returning all of the row we want to return? or more rows? If we are getting multiple rows matched, then which of the rows do we actually want to match... is the cardinality right?)

Then add the join to organisation_group

SELECT a.id
     , a.group_name
     , a.role_id
     , g.group_id
     , o.name
  FROM account a
  JOIN account_group g
    ON g.account_id = a.id
  JOIN organisation_group o
    ON o.id = g.group_id
 ORDER BY a.id

Are the right rows being returned? Then we can add the expression to derive the role_id to the SELECT list.

SELECT a.id
     , a.group_name
     , a.role_id
     , g.group_id
     , o.name

     , CASE o.name 
       WHEN 'Social Worker' THEN 50
       WHEN 'Foster Care'   THEN 26
       ELSE NULL
       END AS new_role_id

  FROM account a
  JOIN account_group g
    ON g.account_id = a.id
  JOIN organisation_group o
    ON o.id = g.group_id
 ORDER BY a.id

Are there any NULL values in group_name, or values of group_name that don't match? Is the new_role_id column contain the value we want to assign, is it correct for every row? Are there any rows being returned that we don't want to update? Do we want to set role_id to NULL for name other than 'Social Worker' and 'Foster Care' , or do we want to leave it as it is? (by replacing NULL with a.role_id)?

Once we have a SELECT statement working, convert that to an UPDATE.

We just replace SELECT ... FROM with UPDATE keyword, and add a SET clause before the WHERE clause (or in this case, before the ORDER BY clause.)

In this example, we can assign the new_role_id expression to the role_id column.

UPDATE account a
  JOIN account_group g
    ON g.account_id = a.id
  JOIN organisation_group o
    ON o.id = g.group_id
   SET a.role_id
     = CASE o.name 
       WHEN 'Social Worker' THEN 50
       WHEN 'Foster Care'   THEN 26
       ELSE NULL
       END
 ORDER BY a.id

This is the approach I use to building multi-table update statements. I'm not smart enough to start out writing the UPDATE statement. I use a step-wise refinement to build the SELECT statement, because I want to be sure that I know what the statement is going to do. By examining the results returned from the SELECT, I can do the verification. Before I garf up a table with an UPDATE.

Answer 2

First in your where condition you are referring to a table.column WHERE organisation_group.name that is not visible at top level coul be you need account.name Second, a suggestion, you have not aggregation function i subquery so if you need distinct value sue distinct clause

UPDATE account 
SET account.role_id = (CASE WHEN organisation_group.name = "Social Worker" THEN 50
                            WHEN organisation_group.name = "Foster Carer" THEN 26
                       END)
WHERE account.name in 
    ( SELECT distinct organisation_group.name FROM organisation_group 
      INNER JOIN account_group ON organisation_group.id = account_group.group_id 
      WHERE organisation_id = 11 
     );

but If i understand right your join rules you should join this way

UPDATE account 
INNER JOIN account_group on account_group.account_id = account.id
INNER JOIN organisation_group on organisation_group.id = account_group.group_id 
SET account.role_id = (CASE WHEN organisation_group.name = "Social Worker" THEN 50
                        WHEN organisation_group.name = "Foster Carer" THEN 26
                   END)
WHERE organisation_group.id = 11