用于更新查询错误的CASE语句

I am using the CASE statement to Update some table data but its giving error incorrect error in WHEN and THEN Condition.any body can tell whats wrong with this.here is my code,

我正在使用CASE语句更新一些表数据，但它在WHEN和THEN Condition.any主体可以告诉我们错误的错误错误。这是我的代码，

UPDATE TBL_AMOUNT_SETTING 
SET CALCULATED_AMOUNT =
        CASE
           WHEN EXISTS(SELECT COUNT(*) 
                       FROM TBL_GAME_PRICE_BY_DATE 
                       WHERE GAMEPRICE_DATE = CONVERT(DATE,GETDATE())) 
             THEN 
                (SELECT GAMEPRICE 
                 FROM TBL_GAME_PRICE_BY_DATE 
                 WHERE GAMEID = 10 
                   AND GAMEPRICE_DATE = CONVERT(DATE,GETDATE()))
           ELSE NULL
        END
 WHERE REF_GAME_ID = 10

3 个解决方案

#1

You sql is wrong, you miss the case keyword

你的SQL错了，你错过了case关键字

The template is as below:

模板如下：

select case 
  when exists (
  ...
  ) 
  then ...
  else ...
  end
  where ...

Same for update:

更新相同：

  update tbl set field =
  case 
  when exists (
  ...
  ) 
  then ...
  else ...
  end
  where ...

#2

The original problem with your question is missing Case keyword in your update query.

您的问题的原始问题是更新查询中缺少Case关键字。

I think a better do this could be separating the process of your update statement. Since none of your subquery is correlated you can try this

我认为更好的做法是将更新语句的过程分开。由于您的子查询都没有相关，您可以尝试这样做

This is more readable then what you are doing

这比你正在做的更具可读性

DECLARE @gameprice NUMERIC(22, 6) 

IF EXISTS(SELECT Count(*) 
          FROM   tbl_game_price_by_date 
          WHERE  gameprice_date = CONVERT(DATE, Getdate())) 
  SELECT @gameprice = gameprice 
  FROM   tbl_game_price_by_date 
  WHERE  gameid = 10 
         AND gameprice_date = CONVERT(DATE, Getdate()) 

UPDATE tbl_amount_setting 
SET    calculated_amount = @gameprice 
WHERE  ref_game_id = 10

#3

Try it ..

尝试一下 ..

UPDATE tbl_amount_setting 
SET    calculated_amount = ( CASE 
                               WHEN EXISTS(SELECT Count(*) 
                                           FROM   tbl_game_price_by_date 
                                           WHERE  gameprice_date = 
                                                  CONVERT(DATE, Getdate())) THEN 
                               (SELECT Max(gameprice) 
                                FROM 
                             tbl_game_price_by_date 
                               WHERE 
                             gameid = tbl_amount_setting.ref_game_id 
                             AND gameprice_date = CONVERT(DATE, Getdate())) 
                               ELSE NULL 
                             END ) 
WHERE  ref_game_id = 10

#1