sql - 当所有参数(在 where 子句之后)来自一行时，缩短/减少条件/参数

Question

下面的代码运行良好，但我觉得它太长了，
我想在将“MealNo”字段中的值“1”删除之前将其记录到具有字段 MealNo=2 的行

下面的代码完成了这项工作，但我看到它很长，有什么办法可以缩短它？尤其是在“where”之后

 if ((SELECT MealNo FROM [Temp_Food_TBL] WHERE [AutoNo] = @AutoNo) = 1)
    BEGIN 
        Update [Temp_Food_TBL]
        SET
         MealNo = 1
         Where
         MealNo = 2
         AND
         PersonID = (SELECT PersonID FROM [Temp_Food_TBL] WHERE [AutoNo] = @AutoNo)
         AND
         MealTime = (SELECT MealTime FROM [Temp_Food_TBL] WHERE [AutoNo] = @AutoNo)
         AND
         MealDate = (SELECT MealDate FROM [Temp_Food_TBL] WHERE [AutoNo] = @AutoNo)  
    END

 DELETE FROM [Temp_Food_TBL]
   WHERE 
 [AutoNo] = @AutoNo

Answer 1

您可以尝试自连接而不是使用 WHERE声明。

if ((SELECT MealNo FROM [Temp_Food_TBL] WHERE [AutoNo] = @AutoNo) = 1)
    BEGIN 
        UPDATE t1
        SET t1.MealNo = 1
        FROM [Temp_Food_TBL] AS t1
        INNER JOIN [Temp_Food_TBL] AS t2
          ON t1.PersonID = t2.PersonID
            AND t1.MealTime = t2.MealTime
            AND t1.MealDate = t2.MealDate
            AND t1.MealNo = 2 
        WHERE t2.AutoNo = @AutoNo 
    END 

   DELETE FROM [Temp_Food_TBL]
   WHERE [AutoNo] = @AutoNo

这并不是真的更短。但是有几个用于查找匹配行的标准，我不确定您是否可以将其全部缩短。