问题:

Basically what I want to do is copy the value of a column from one table to another column in another table.

The query I am using is:

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.

How can I do this?

回答1:

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

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.

Use the old value if the query returns null:

UPDATE t1 
SET product_code = 
ifnull(
    (SELECT `value` FROM t2 WHERE t2.variant_id = t1.variant_id AND key_id = 10), 
    product_code);

FYI, in mysql using the old value doesn't count as an "update" in any way (either the number of rows affected or the logged changes)