来自另一个表的SQLITE UPDATE字段IF NULL

编程入门行业动态更新时间:2024-10-26 10:28:31

本文介绍了来自另一个表的SQLITE UPDATE字段IF NULL的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！问题描述

我有一个 UPDATE 更新.

我让它工作了，但我想知道我的解决方案是否正确或缺少什么，我是否可以以更紧凑，更快速的方式编写它.

I Got it working but I'm wondering if my solution is correct or something is missing and if I could write it in a more compact and speeder way.

此外，我的代码非常难以管理，因为如果将来表结构会改变，我将必须重写查询以添加或删除字段.

Moreover my code is very very unmanageable, because of if in the future the table structure will change I'll must rewrite the query to add or remove fields.

如果要写一个 sql ，我在徘徊a>查询，它从一个表中获取一条完整的记录，并更新同一表的另一条记录中的所有 NULL 字段.

I was wandering if it's possible to write a sql query that take a whole record from a table and update all NULL fields in another record of the same table.

sprintf(global_sql, "UPDATE "\ "Table1 "\ "SET "\ "fiedl1 = case when fiedl1 is null then (SELECT fiedl1 FROM Table1 WHERE Table1_id = %d) else fiedl1 end, "\ "fiedl2 = case when fiedl2 is null then (SELECT fiedl2 FROM Table1 WHERE Table1_id = %d) else fiedl2 end, "\ "fiedl3 = case when fiedl3 is null then (SELECT fiedl3 FROM Table1 WHERE Table1_id = %d) else fiedl3 end "\ "WHERE "\ "Table1_id = (SELECT max(Table1_id) FROM Table1)", sourceRecord_idx, sourceRecord_idx, sourceRecord_idx);

如您所见，对于每个单个字段，我必须写出其特定的 SET 大小写，此外，每个字段的值都可以通过select检索.

As you can see for each single field I must write its specific SET case and moreover the value for each filed is retrieved by a select.

我猜/希望有一种更简单，更可维护的方式来实现这一目标.

I guess/hope there is a simplier and more maintainable way to do that.

推荐答案

您可能会认为这更易于维护，至少它少了一些三联代码":

You might consider this slightly more maintainable, at least it has some "triple-copied-code" less:

with shortcut(f1, f2, f3) as (SELECT fiedl1, fiedl2, fiedl3 FROM Table1 WHERE Table1_id = %d) UPDATE Table1 SET fiedl1 = ifnull(fiedl1, (SELECT f1 FROM shortcut)), fiedl2 = ifnull(fiedl2, (SELECT f2 FROM shortcut)), fiedl3 = ifnull(fiedl3, (SELECT f3 FROM shortcut)) WHERE Table1_id = (SELECT max(Table1_id) FROM Table1) ;

详细信息: