Question

我正在设法弄清楚是否可以检查表B中是否有具体记录。如果是的话,请不要更新表A。

我尝试了谷歌,但我只找到了插入版本,我不确定能否更新查询。

提前感谢

Answer 1

update table_to_update 
set some_column = 123
where id = 1
and id not in (select id from table_b)

Answer 2

The following SQL statements function as a foreign key in where I add a new field in the daughter table with the condition that this value does not exist in a column of the master table.

UPDATE t1
SET c1 = ?
WHERE t1.id = ?
AND ? NOT IN (SELECT id FROM t2);

参数和表格的含义:

t1: daughter table
t2: master table
1st ? : Value that will be established in the field of t1.c1 if the condition is met
2nd ? : Identifier of the record where the field to be updated is located
3rd ? : Value necessary to evaluate the condition, where I compare it with the field of t2

< 强 > > 第一参数和第三参数的值必须相同!

使用此语句时,当我们指主表格字段时,列必须有一个主密钥。这就是为什么与外国密钥相似的原因。

第二个备选方案还将是:

UPDATE t1 a
LEFT JOIN t2 b
ON b.column = ?
SET t1.c1 = ?
WHERE b.column IS NULL && a.id = ?;

< 加强> 第一和第二参数必须是相同的。在 < code> column 是主表一列的地方。

“它们”另一方面,在使用此其他句子时,我们具有更大的灵活性,因为它不是主表列有一个主键的必要条件。

Answer 3

您也可以尝试使用 MySQL < a href="http://dev.mysql.com/doc/refman/ 5.0/ en/ existences-and-not- existences-subqueries.html" rel="nofollow" > sexistences 表达式。旧的 MySQL 版本使用语法, 即 5. 0, 证明我比使用嵌套查询更快。

update table_a
set some_column =  bla 
where not exists (select id
                        from table_b
                        where table_a.id = table_b.id)

友情链接