英文:
Join in subquery failing on MySQL 5.7
问题
I'm writing a data migration in SQL for an app that has to support multiple possible databases, including MySQL 5.7. Here is the code that I currently have:
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;
This fails on MySQL 5.7 with the error Unknown column 's.table_id' in 'on clause'. I'm assuming the ON clause is more strict in 5.7 than in later versions since it works in other versions of MySQL as well as in Postgres. Is there any way to get around this limitation? Or a way I can rewrite the query? I'm not great with SQL, so any help is appreciated.
英文:
I'm writing a data migration in SQL for an app that has to support multiple possible databases, including MySQL 5.7. Here is the code that I currently have:
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;
This fails on MySQL 5.7 with the error Unknown column 's.table_id' in 'on clause'. I'm assuming the ON clause is more strict in 5.7 than in later versions since it works in other versions of MySQL as well as in Postgres. Is there any way to get around this limitation? Or a way I can rewrite the query? I'm not great with SQL so any help is appreciated.
答案1
得分: 1
不清楚为什么会出现错误，但您可以通过在UPDATE查询中使用JOIN而不是子查询来解决它。
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
英文:
It's not clear why you're getting the error, but you can solve it by using a JOIN in the UPDATE query rather than a subquery.
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论