使用其他表中的数据更新表(如果不为null)? [英] update table with data from other table if not null?
本文介绍了使用其他表中的数据更新表(如果不为null)?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
基本上我想做的是将一列的值从一个表复制到另一表的另一列.
Basically what I want to do is copy the value of a column from one table to another column in another table.
我正在使用的查询是:
UPDATE t1
SET product_code =
(SELECT `value` FROM t2 WHERE t2.variant_id = t1.variant_id AND key_id = 10);
哪个工作正常,但列不匹配
Which is working fine, but there is a mismatch in columns,
所以我需要添加一个子句,如果子查询不返回空值,则该子句只会对该行进行更新.
so I need to add in a clause which will only do the update on that row, if the subquery does not return null.
我该怎么做?
推荐答案
您应该像这样通过联接进行更新
You should just being doing the update across a join like this
UPDATE
t1 INNER JOIN t2 ON t1.variant_id = t2.variant_id
SET t1.product_code = t2.value
WHERE t2.key_id = 10
AND t2.value IS NOT NULL
在这种情况下,无需担心空值,因为内部联接只会选择两个表中都存在variant_id的行.
There is no need to worry about nulls in that case as the inner join will only select rows where the variant_id exists in both tables.
这篇关于使用其他表中的数据更新表(如果不为null)?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文