Update table, set ID based on relation. Column not found-CodePudding

I have the following update statement, but it says [42S22][1054] Unknown column 'b.user_id' in 'on clause'

UPDATE brands b set b.workspace_id = (
  SELECT w.id from workspaces w
    INNER JOIN users u on b.user_id = u.id
    inner join team_members tm on u.id = tm.user_id
    inner join teams t on tm.team_id = t.id AND w.id = t.workspace_id
);

Basically, there are brands and workspaces. A new column workspace_id was added as foreign key, and the workspace id can be found through the relation brand -> has user_id -> user has team -> team has workspace_id

In a programming side I could find first all workspaces to process, then get all user ids for that workspace, then run a update brands b set workspace_id = :wsId where user_id in (:userIds)

-- auto-generated definition
create table brands
(
    id           bigint auto_increment
        primary key,
    user_id      int unsigned                  not null,
    name         varchar(100)                  null,
    workspace_id int                           null
)

CodePudding user response：

You can't use the column from brands in a join condition, but you can use it in a where condition in the sub-query

create table brands (
  workspace_id int,
  user_id int
  );
create table users (
  id int);
create table team_members(
  user_id int,
  team_id int
  );
create table teams (
  id int,
  workspace_id int
  );
create table workspaces(
  user_id int,
  id int
  );

UPDATE brands set workspace_id = (
  SELECT w.id from workspaces w
    INNER JOIN users u on w.user_id = u.id
    inner join team_members tm on u.id = tm.user_id
    inner join teams t on tm.team_id = t.id AND w.id = t.workspace_id
    WHERE brands.user_id = u.id
);

db<>fiddle here