来自另一个表的SQLITE UPDATE字段IF NULL [英] SQLITE UPDATE field IF NULL from another table

问题描述

我有一个 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)
;

详细信息:

• 使用CTE减少重复条件和其他快捷方式
• 使用 ifnull 使条件值更简单，更短

• use a CTE for less repetition of conditions and other shortcutting
• use ifnull for making the conditional value simpler, shorter

在SQLite 3.9.1上进行了测试，使用固定值而不是％d .

Tested on SQLite 3.9.1, using a fixed value insteadof %d.

这篇关于来自另一个表的SQLITE UPDATE字段IF NULL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！