MySQL 替换为 null [英] MySQL replace into with null

问题描述

我正在使用替换为用另一个表中的信息更新一个表.第一个表称为 srv_update，它包括:

I am using replace into to update one table with the information in another. The first table is called srv_update and it includes:

srv_id,    , srv_list_price,   srv_list_price
'120', 'Third Feline PCR', '20.25'
'121', 'Flu Rhino Vacc', '26.00'
'122', 'Scaly Mite', '35.00'
'123', 'Intestinal Parasite Screen', '26.00'
'124', 'Tick Removal', '15.00'
'125', 'Behaviour Modification', '75.00'
'126', 'Vitamin E- Concentrated', '30.00'
'127', 'Sedative-Feline', '25.00'
'128', 'Flea Treatment- Small Animal', '35.00'
'129', 'Flea Treatment- Large Animal', '50.00'

第二个表是 srv_update_changes_2，它包括:

the second table is srv_update_changes_2 and it includes:

item_id,   item_desc,      item_list_price
'134', 'Vitamin E series', '55.00'
'135', 'Feline PCR Series', '75.00'
'114', 'H1N1 vaccine', '75.00'
'115', 'H1N2 vaccine', '75.00'
'127', 'Sedative- small mammal', '75.00'
'111', 'Rabies_V-Rodent', '25.00'
'129', NULL, '25.00'

我只想替换现有 ID 而不想插入任何新 ID.另外如果desc字段为空(它是第二个表的id 129)，在替换空字段时应该保留原始表的值(第一个表中的描述)

I want to only replace existing IDs and not insert any new ID's. Also if the desc field is null (it is for id 129 of the second table) when replacing the null field should keep the value of the original table (the descripton in the first table)

129 是唯一应该受到影响的行.我将它添加到表格中以试图弄清楚这一点，但我似乎无法理解逻辑.我试过 ifnull 和我试过合并，但我似乎无法得到正确的输出.它要么在更新的表中保持空值，要么插入错误的描述.

129 is the only row that should be affected. I added it to the table to try to figure this out but I cant seem to get the logic. Ive tried ifnull and ive tried coalesce but i cant seem to get the right output. it either stays null in the updated table or inserts the wrong description.

我还尝试合并，其中第二个字段是一个子查询，用于检查第一个字段是否为空，以将其替换为第一个表中的服务描述，其中该行项目 ID 与第二个表中的 srv id 匹配，其 desc 行为空

Ive also attempted coalesce where the second field is a subquery that checks if the first field is null to replace it with the service description from the first table where that rows item id matched the srv id in the second table where its desc row is null

replace into upd_services
select item_id,
coalesce(item_desc,(
    select srv_desc 
from upd_services 
where srv_id in(
    select item_id
    from upd_services_changes_2
    where item_desc is null))) srv_desc,
item_list_price
from upd_services_changes_2 
where item_id in (
select srv_id
from upd_services)

感谢您的帮助

推荐答案

我会将 update 与 join 一起使用:

I would use update with a join:

update upd_services us join
       upd_services_changes_2 usc
       on us.srv_id = usc.item_id
    set us.srv_desc = coalesce(usc.item_desc, us.srv_desc),
        us.srv_list_price = usc.item_list_price;

这篇关于MySQL 替换为 null的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！