|
我有这样的查询(在函数中):
/ Y- C7 U) L, X) H8 U9 a$ \/ zUPDATE some_table SET column_1 = param_1, column_2 = param_2, column_3 = param_3, column_4 = param_4, column_5 = param_5WHERE id = some_id;param_x我的函数参数在哪里?有没有办法不更新这些参数的列?NULL?例如-
/ L/ p9 O0 E* A; p* C+ K) W如果param_4和param_5是NULL,然后只更新前三列,离开旧值column_4和column_5。
% {! ~/ L- X& |+ @8 r0 i9 J2 o我现在做的是:
6 |- c( V- X) t# m7 F4 D9 kSELECT * INTO temp_row FROM some_table WHERE id = some_id;UPDATE some_table SET column_1 = COALESCE(param_1,temp_row.column_1), column_2 = COALESCE(param_2,temp_row.column_2), column_3 = COALESCE(param_3,temp_row.column_3), column_4 = COALESCE(param_4,temp_row.column_4), column_5 = COALESCE(param_5,temp_row.column_5)WHERE id = some_id;有没有更好的办法?
1 q4 L& `; q C2 l$ H$ p 9 f6 F8 \, N9 a6 q& A
解决方案:
9 r# a0 ^; q: L- f- }" [ 删除SELECT句子,不需要使用,只需要使用当前值:' L# C" U: a4 v+ t+ c! @
UPDATE some_table SET column_1 = COALESCE(param_1,column_1), column_2 = COALESCE(param_2,column_2), column_3 = COALESCE(param_3,column_3), column_4 = COALESCE(param_4,column_4), column_5 = COALESCE(param_5,column_5)WHERE id = some_id; |
|