更新问题 [英] Update question
问题描述
假设我有20列的表格。
我的商店proc需要两个输入参数（param1，param2），现在我需要
UPDATE列根据param1使用param2：
例如
如果param1 = 222 - >更新col1
如果param1 = 333 - >更新col2
..................
.............. ....
............................
是否有可能在没有写入IF和更新的20倍的情况下完成它？
-
消息通过 http://www.sqlmonster.com
Suppose i have table with 20 columns.
My store proc takes two input parameters (param1, param2), now i need to
UPDATE column with param2 according to param1:
e.g
if param1 = 222 --> UPDATE col1
if param1 = 333 --> UPDATE col2
..................
..................
............................
Is it possible to acomplish it wiithout write 20 times the IF and UPDATE ??
--
Message posted via http://www.sqlmonster.com
推荐答案
你当然可以使用CASE语句来做到这一点：
UPDATE YourTable
SET col1 = CASE WHEN @ param1 = 222那么@param2 ELSE col1结束，
col2 = CASE WHEN @ param1 = 333那么@param2 ELSE col2 END，
...
WHERE ...
这似乎比一组IF语句更简洁，但它可能不会那么高效。
你想要表达的逻辑究竟是什么？我想不出那个想要以这种方式传递参数的好理由。似乎
如果你需要通过某种索引引用许多
列，它可能表示设计缺陷。
- -
David Portas
SQL Server MVP
-
You could certainly use a CASE statement to do this:
UPDATE YourTable
SET col1 = CASE WHEN @param1 = 222 THEN @param2 ELSE col1 END,
col2 = CASE WHEN @param1 = 333 THEN @param2 ELSE col2 END,
...
WHERE ...
That might seem more concise than a set of IF statements but it
probably won''t be as efficient.
What exactly is the logic you are trying to represent? I can''t think of
many good reasons for wanting to pass the parameters that way. Seems
like it may indicate a design flaw if you need to reference many
columns by some sort of index.
--
David Portas
SQL Server MVP
--
谢谢你大卫，
但是我使用的技巧你建议我但是效率不高，BTW是
优化器不能自己更新这个领域？ ？
我的逻辑是当某个事件发生时（param1）然后我需要更新
某些字段。
任何想法？
谢谢
-
留言通过 http://www.sqlmonster.com
Thank u David,
However i used technique that u advice me but it''s not efficient, BTW is
optimizer don''t UPDATE the field with itself??
My logic is that when some event occured (param1) then i need to update
some field.
Any ideas??
Thanks
--
Message posted via http://www.sqlmonster.com
有人有任何ieas ???
-
消息通过 http://www.sqlmonster.com
Somebody have any ieas???
--
Message posted via http://www.sqlmonster.com
这篇关于更新问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
