我有一个包含多列的表,现有数据已经像一样
+------+------+--------------+------+---------+|id | ref | name | role | status|+------+------+--------------+------+---------+|1|ab|Faizal|adm|1||2|ab|Faizal||0||3|cd|Faizal|usr|1||4|am|Agrim|usr|1||5|xy|vishal|usr|1||6|xy|vishal|usr|0||7|ad|ankush|usr|1||8|ad|ankush||0||9|mm|vishal|oth|1|+------+------+--------------+------+---------+
并且我不想在表中多次为用户分配角色,只有当表中用户没有相同角色且状态相同时,Update才应该采取行动。就像我试过那个查询
UPDATE roles t1
SET
t1.role = 'adm',
t1.status = '1',
t1.ref = 'ab'
WHERE t1.id = 2 AND NOT EXISTS
(
SELECT 1
FROM roles t2
WHERE t1.role = t2.role
AND t1.status = t2.status
AND t1.ref = t2.ref
)
它会产生错误我想在执行查询后:
+------+------+--------------+------+---------+|id | ref | name | role | status|+------+------+--------------+------+---------+|1|ab|Faizal|adm|1||2|ab|Faizal||0||3|cd|Faizal|usr|1||4|am|Agrim|usr|1||5|xy|vishal|usr|1||6|xy|vishal|usr|0||7|ad|ankush|usr|1||8|ad|ankush||0||9|mm|vishal|oth|1|+------+------+--------------+------+---------+
没有更改因为表中已经存在给定的键数据。
另一个示例:使用其他关键数据运行查询。
UPDATE roles t1
SET
t1.role = 'adm',
t1.status = '1',
t1.ref = 'ad'
WHERE t1.id = 8 AND NOT EXISTS
(
SELECT 1
FROM roles t2
WHERE t1.role = t2.role
AND t1.status = t2.status
AND t1.ref = t2.ref
)
执行查询表后应:
+------+------+--------------+------+---------+|id | ref | name | role | status|+------+------+--------------+------+---------+|1|ab|Faizal|adm|1||2|ab|Faizal||0||3|cd|Faizal|usr|1||4|am|Agrim|usr|1||5|xy|vishal|usr|1||6|xy|vishal|usr|0||7|ad|ankush|usr|1||8|ad|ankush||0||9|mm|vishal|oth|1|+------+------+--------------+------+---------+
请记住条件:
- 我们不能在表中插入或删除
- 无法更新状态为1的行
在update
语句期间,对该表中记录的任何引用仍然与实际更新之前的数据有关,因此子选择不考虑您正在插入的值,而是考虑表中已经存在的值,这是正常的。
此外,MySql不允许使用这种带有自引用子查询的语法。请改用left join
。
因此,在连接条件下,列出要更新的文字值,如下所示:
UPDATE roles t1
LEFT JOIN roles t2
ON t2.role = 'adm'
AND t2.status = '1'
AND t2.ref = 'ab'
SET
t1.role = 'adm',
t1.status = '1',
t1.ref = 'ab'
WHERE t1.id = 2
AND t2.id IS NULL
最后一个条件对应于您的NOT EXISTS
。当JOIN
条件未产生结果时,LEFT JOIN
将在所有t2列中产生NULL
值。
如果您在这三个字段上有一个唯一的密钥约束,那么您可以使用ignore
关键字实现相同的约束:
UPDATE IGNORE roles
SET
role = 'adm',
status = '1',
ref = 'ab'
WHERE id = 2
如果这将导致重复,则该语句将不会执行任何操作,也不会报告错误。