mysql - 如何解决错误“您无法在 FROM 子句中指定目标表 'users' 进行更新”

Question

insert into users(email, is_CM, password, username)
    values ('[e-mail]','N','*****','sidd@12345')
    ON DUPLICATE KEY UPDATE username=(SELECT username FROM users WHERE username = VALUES(username) OR email= VALUES(email));

我正在尝试运行此查询，但出现错误 ERROR 1093 (HY000): You can't specify target table 'users' for update in FROM 子句

标签： mysqlsql

Answer 1

那里通常不使用子查询。我希望：

insert into users (email, is_CM, password, username)
    values ('[e-mail]', 'N', '*****',' sidd@12345')
    on duplicate key update username = values(username), email = values(email));

这会将现有行设置为传递给查询的值。