MySQL更新昆德和配置文件的多个表

Question

我有一个“侧面”和“昆德”的桌子。"profil“得到了邮箱和密码。“昆德”从“个人资料”中获得了个人简介、用户名和电子邮件作为索引。“昆德”得到了主键用户名。我想立即更新用户名bio和密码

UPDATE profil, kunde SET kunde.username = ?, kunde.bio = ?, profil.passwort = ? WHERE profil.email = ?;

我得到的错误代码

#1062 - duplicated entry 'Kneipengänger60' for key 'PRIMARY'

Answer 1

不能在一条语句中更新两个表。但是，您可以使用事务来确保这两个语句作为一个单元进行更新。如果其中一个失败，两个都会回滚。

BEGIN TRANSACTION;

UPDATE profil
SET profil.passwort = ?
FROM profil, kunde
WHERE profil.id = kunde.id //Here you use the secondary key and primary keys to link the tables
and profil.email = ?;

UPDATE kunde
SET kunde.username = ?, kunde.bio = ?
FROM profil, kunde
WHERE profil.id = kunde.id //Here you use the secondary key and primary keys to link the tables
and profil.email = ?;

COMMIT;