如何修复此 MySQL 查询使其正常工作？

发布于 2024-07-08 08:04:42 字数 542 浏览 3 评论 0原文

我有以下查询：

UPDATE lessonstatus
INNER JOIN user ON lessonstatus.user_id = user.user_id
SET user_id = (SELECT user_id FROM user WHERE username = 'too_many_accounts')
WHERE last_name = 'stupid' 
AND first_name = 'user'
AND username != 'too_many_accounts'
AND lessonstatus.lesson_id NOT IN (SELECT lesson_id FROM lessonstatus WHERE user_id = 1);

但是，在尝试执行它时出现以下错误：

Error Code : 1093
You can't specify target table 'lessonstatus_rtab' for update in FROM clause

如何修复此查询以使其正常工作？

原文

I have the following query:

UPDATE lessonstatus
INNER JOIN user ON lessonstatus.user_id = user.user_id
SET user_id = (SELECT user_id FROM user WHERE username = 'too_many_accounts')
WHERE last_name = 'stupid' 
AND first_name = 'user'
AND username != 'too_many_accounts'
AND lessonstatus.lesson_id NOT IN (SELECT lesson_id FROM lessonstatus WHERE user_id = 1);

However, I get the following error when trying to execute it:

Error Code : 1093
You can't specify target table 'lessonstatus_rtab' for update in FROM clause

How would I fix this query so that it works?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

孤寂小茶 2024-07-15 08:04:42

您无法从在同一查询中更新的表（即使在子查询中）中进行SELECT。这就是错误“无法指定目标表”的含义。

但是您可以在 UPDATE 语句中多次联接 user 和 lessonstatus，并创造性地使用联接条件来挑选所需的单个行。

使用连接模拟 NOT IN 的方法是执行 LEFT OUTER JOIN。如果该连接的右侧不匹配，则 NOT IN 为 true。

UPDATE lessonstatus l1
  INNER JOIN user u1 ON (l1.user_id = u1.user_id)
  INNER JOIN user u2 ON (u2.username = 'too_many_accounts')
  LEFT OUTER JOIN lessonstatus l2 
    ON (l1.lesson_id = l2.lesson_id AND l2.user_id = 1)
SET l1.user_id = u2.user_id
WHERE u1.last_name = 'stupid' AND u1.first_name = 'user'
  AND u1.username != 'too_many_accounts'
  AND l2.lesson_id IS NULL; -- equivalent to "l NOT IN l2"

注意：我已经测试了此查询的语法，但没有使用真实数据。无论如何，它应该让你开始。

You can't SELECT from a table (even in a subquery) that you're updating in the same query. That's what the error "can't specify target table" means.

But you can join user and lessonstatus multiple times in the UPDATE statement, and use the join criteria creatively to pick out the individual row you want.

The way to simulate NOT IN with a join is to do a LEFT OUTER JOIN. Where the right side of that join is not matched, that's where NOT IN would be true.

UPDATE lessonstatus l1
  INNER JOIN user u1 ON (l1.user_id = u1.user_id)
  INNER JOIN user u2 ON (u2.username = 'too_many_accounts')
  LEFT OUTER JOIN lessonstatus l2 
    ON (l1.lesson_id = l2.lesson_id AND l2.user_id = 1)
SET l1.user_id = u2.user_id
WHERE u1.last_name = 'stupid' AND u1.first_name = 'user'
  AND u1.username != 'too_many_accounts'
  AND l2.lesson_id IS NULL; -- equivalent to "l NOT IN l2"

nb: I have tested this query for syntax, but not with real data. Anyway, it should get you started.

回复收藏 0 原文

霊感 2024-07-15 08:04:42

还有更多错误（“user”表和“user_rtab”别名不匹配，不建议使用非限定字段名称），但 UPDATE 语法本身应该类似：

UPDATE lessonstatus
SET user_id = (SELECT TOP 1 user_id FROM user WHERE username = 'too_many_accounts')
FROM lessonstatus
    INNER JOIN user ON lessonstatus.user_id = user_rtab.user_id
WHERE last_name = 'stupid' 
    AND first_name = 'user'
    AND username != 'too_many_accounts'
    AND lessonstatus.lesson_id NOT IN (
        SELECT lesson_id FROM lessonstatus WHERE user_id = 1
    );

There are more errors ("user" table and "user_rtab" alias do not match, use of non-qualified field names is not recommended), but UPDATE syntax itself should be similar:

UPDATE lessonstatus
SET user_id = (SELECT TOP 1 user_id FROM user WHERE username = 'too_many_accounts')
FROM lessonstatus
    INNER JOIN user ON lessonstatus.user_id = user_rtab.user_id
WHERE last_name = 'stupid' 
    AND first_name = 'user'
    AND username != 'too_many_accounts'
    AND lessonstatus.lesson_id NOT IN (
        SELECT lesson_id FROM lessonstatus WHERE user_id = 1
    );

回复收藏 0 原文

~没有更多了~