SQL UPDATE、SELECT CASE with Division和INNER

Question

我尝试了很多方法来做，但没有任何一个，我需要更新数量，只有当在t2中的除法在t1.total和t1之间的数量在t2中是update

我的代码：

UPDATE t1
SET t1.Code_142 = 'OK' 
SELECT CASE WHEN quantity = 0 THEN NULL ELSE TOTAL/quantity END AS q
FROM Servicios_de_control_de_calidad_de_agua_potable as t1
INNER JOIN Con_A as t2 ON t1.ID = t2.ID
WHERE t1.q BETWEEN t2.Mins AND t2.Maxs

问候

Answer 1

您要查找的查询应如下所示

UPDATE t1
SET t1.Code_142 = 'OK' 
FROM Servicios_de_control_de_calidad_de_agua_potable  t1
INNER JOIN Con_A  t2 ON t1.ID = t2.ID
WHERE CASE WHEN quantity = 0 THEN t2.Mins-1 ELSE TOTAL/quantity END BETWEEN t2.Mins AND t2.Maxs

Answer 2

您可以按照下面的步骤进行尝试，希望能对您有所帮助

UPDATE t1
SET t1.Code_142 = 'OK' 
FROM Servicios_de_control_de_calidad_de_agua_potable as t1
INNER JOIN Con_A as t2 ON t1.ID = t2.ID
WHERE (CASE WHEN quantity = 0 THEN NULL ELSE TOTAL/quantity END) BETWEEN t2.Mins AND t2.Maxs

Answer 3

显然，我无法测试这段代码，但您可能需要以下代码：

UPDATE t1
SET t1.Code_142 = 'OK',
    q = CASE WHEN quantity = 0 THEN NULL ELSE TOTAL/quantity END 
FROM Servicios_de_control_de_calidad_de_agua_potable as t1
INNER JOIN Con_A as t2 ON t1.ID = t2.ID
WHERE t1.q BETWEEN t2.Mins AND t2.Maxs