Subquery insert fails in MySQL 5.7

Question

I'm writing SQL data migrations for an application that needs to support multiple possible databases, including MySQL 5.7. Here is my current code:

UPDATE sandboxes s
SET permission_id = (
  SELECT p.id
  FROM permissions p
  JOIN tables t ON t.id = s.table_id
  WHERE
    p.object LIKE CONCAT('/db/', t.db_id, '/schema/', t.schema, '/table/', s.table_id, '/query/segmented/')
    AND p.group_id = s.group_id
  LIMIT 1
)
WHERE permission_id IS NULL;

Error when running on MySQL 5.7Unknown column 's.table_id' in 'on clause'. I'm guessing that in version 5.7, the ON clause is more strict than in subsequent versions, since it works fine in other versions of MySQL and Postgres. Is there any way to bypass this restriction? Or is there a way to rewrite the query? I'm not very good with SQL so any help would be greatly appreciated.

Answer 1

It's not clear why the error occurs, but you can fix it by using JOIN instead of a subquery in the UPDATE query.

UPDATE sandboxes s
JOIN permissions AS p on p.group_id = s.group_id
JOIN tables AS t ON t.table_id = s.table_id AND p.object = CONCAT('/db/', t.db_id, '/schema/', t.schema, '/table/', s.table_id, '/query/segmented/')
SET s.permission_id = p.id