如果不为空，则使用其他表中的数据更新表?

update table with data from other table if not 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,

所以我需要添加一个子句，如果子查询不返回 null，它只会对该行进行更新.

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.

这篇关于如果不为空，则使用其他表中的数据更新表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！