mysql - perl - insert value if column word is missing

Question

in perl, i want to insert missing line if

database 1                               database 2
-----------------------------------      ----------------------------------
table truc                               table truc2
-----------------------------------      ----------------------------------
   parti                                 id   parti missing_line
-----------------------------------     ----------------------------------
   MODEM                                14   MODEM   0
   EELV                                 33   EELV    0
   SP                                    2   SP      0
   CPNT                                  3   CPNT    0

after 5 min i have this, SP and MODEM in pati column is missing, i want to insert 1 if any word in column is missing:

database 1                               database 2
-----------------------------------      ----------------------------------
table truc                               table truc2
-----------------------------------      ----------------------------------
   parti                                id   parti missing_line
-----------------------------------     ----------------------------------
                                        14   MODEM   1
   EELV                                 33   EELV    0
                                         2   SP      1
   CPNT                                  3   CPNT    0

thanks for any response,

my code of perl and sql are here

标签： mysqlsql

Answer 1

您可以使用 MySQLupdate ... join ... set ... where ...语法。使用left joinand where ... is null，您可以识别不匹配的记录：

update database2.truc2 t2
left join database1.truc1 t2 on t2.parti = t1.parti
set t2.missing_line = 1
where t1.parti is null

另一种选择是使用not exists：

update database2.truc2 t2
set t2.missing_line = 1
where not exists (
    select 1 from database1.truc1 t2 where t2.parti = t1.parti
)

我反对使用not in，它在处理null值时会产生意想不到的结果；如果子查询返回的任何值是null，则not in条件实际上成为传递（相当于1 = 1），这将导致源表中的所有记录都被更新。