基于 SELECT 的 SQL 条件更新
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9006666/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Conditional UPDATE Based on SELECT
提问by Damon
In SQL Server 2008, I have a set of data containing costs for East and West. I'm adding a new field for a customer for Canada which needs to be 1.5 times the East or West cost (which ever is greater). So I'm trying to come up with some sql I can execute. I've tried the following but have not had success:
在 SQL Server 2008 中，我有一组包含东西方成本的数据。我正在为加拿大的客户添加一个新字段，该字段需要是东或西成本（以较大者为准）的 1.5 倍。所以我试图想出一些我可以执行的 sql。我尝试了以下但没有成功：
UPDATE ShippingCost
SET
IF EastCost>WestCost
Canada= EastCost*1.8
ELSE
Canada= WestCost*1.8
ENDIF
I'm sure there's an easy way to do this? Any ideas?
我确定有一种简单的方法可以做到这一点？有任何想法吗？
回答by Conrad Frix
You need to use Case
您需要使用案例
UPDATE ShippingCost
SET
Canada = CASE WHEN EastCost>WestCost THEN EastCost*1.8
ELSE WestCost*1.8 END
回答by schtever
Use two update statements:
使用两个更新语句：
UPDATE SHIPPINGCOST SET Canada = EastCost*1.8 WHERE EastCost>WestCost
UPDATE SHIPPINGCOST SET Canada = WestCost*1.8 WHERE EastCost<=WestCost
回答by xphantom
UPDATE ShippingCost SET Canada = GREATEST(EastCoast, WestCoast) * 1.8;
Note:T-SQL dialect does not support GREATEST.
注意：T-SQL 方言不支持GREATEST.
回答by onedaywhen
UPDATE ShippingCost
SET Canada = 1.5 * CASE
WHEN EastCost > WestCost THEN EastCost
ELSE WestCost
END;
