仅更新在同一表中存在匹配行的行

Question

我试图更新一个名为qc的表，只有当first.value等于second.value时。

没有FROM的UPDATE查询更新每个不是我想要的值。我试过这个，但它仍然不工作:

UPDATE qc
SET value = 
CASE 
WHEN value < 125 THEN 
CASE WHEN material LIKE 'Beton%' 
THEN 40
ELSE
(CAST(value - 40 AS DECIMAL(5,2)))

END
WHEN value >= 125 AND value <= 150 THEN
CASE WHEN material LIKE 'Beton%'
THEN 50
ELSE
(CAST(value - 50 AS DECIMAL(5,2)))
END

ELSE 
CASE WHEN material LIKE 'Beton%'
THEN 80
ELSE
(CAST(value - 80 AS DECIMAL(5,2)))
END
END
FROM (SELECT location, value
FROM qc
LEFT JOIN designlocation dl ON qc.designlocationid = dl.designlocationid
WHERE material LIKE 'Beton_v%' AND value != '') first
, (SELECT location, value
FROM qc
LEFT JOIN designlocation dl ON qc.designlocationid = dl.designlocationid
WHERE material LIKE '%(3rd pass)%' AND value !='') second
WHERE first.location = second.location AND first.value = second.value;

Answer 1

UPDATE qc
SET    value = CASE
WHEN dl.material LIKE 'Beton_v%' THEN
CASE
WHEN qc.value < 125               THEN '40'::numeric(5,2)
WHEN qc.value BETWEEN 125 AND 150 THEN '50'::numeric(5,2)
ELSE                                   '80'::numeric(5,2)
END
ELSE
CASE
WHEN qc.value < 125               THEN (qc.value - 40)::numeric(5,2)
WHEN qc.value BETWEEN 125 AND 150 THEN (qc.value - 50)::numeric(5,2)
ELSE                                   (qc.value - 80)::numeric(5,2)
END
END
FROM   designlocation dl
,      qc qc2
JOIN   designlocation dl2 USING (designlocationid)
WHERE  qc.designlocationid = dl.designlocationid
AND    dl.material  LIKE ANY ('{Beton_v%, %(3rd pass)%}')  -- array with 2 patterns
AND    dl2.material LIKE ANY ('{Beton_v%, %(3rd pass)%}')
AND    dl2.material <> dl.material
AND    qc.value <> ''
AND    qc2.location = qc.location
AND    qc2.value    = qc.value
);

为什么Beton_v%vs.Beton%?假设有错别字，使用更严格的模式。字符_在LIKE模式中具有特殊含义。我对它进行了转义，使其成为字面的_。看到:

• 正则表达式或LIKE模式的转义函数

原始查询中的LEFT JOIN都是假的，可以用INNER JOIN代替。看到:

• Postgres LEFT JOIN with WHERE condition

此查询仅更新至少有一个兄弟行存在的(location, value)相同，但material不同的行-LIKE是('Beton_v%', '%(3rd pass)%')之一。

如果有更多这样的行，所有的行都被更新(可能是重复的)。这可能是一个数据错误和/或我们将使用不同的查询。由于没有信息，我只能推测。

Answer 2

UPDATE qc
SET    value = CASE
WHEN dl.material LIKE 'Beton_v%' THEN
CASE
WHEN qc.value < 125               THEN '40'::numeric(5,2)
WHEN qc.value BETWEEN 125 AND 150 THEN '50'::numeric(5,2)
ELSE                                   '80'::numeric(5,2)
END
ELSE
CASE
WHEN qc.value < 125               THEN (qc.value - 40)::numeric(5,2)
WHEN qc.value BETWEEN 125 AND 150 THEN (qc.value - 50)::numeric(5,2)
ELSE                                   (qc.value - 80)::numeric(5,2)
END
END
FROM   designlocation dl
,      qc qc2
JOIN   designlocation dl2 USING (designlocationid)
WHERE  qc.designlocationid = dl.designlocationid
AND    dl.material  LIKE ANY ('{Beton_v%, %(3rd pass)%}')  -- array with 2 patterns
AND    dl2.material LIKE ANY ('{Beton_v%, %(3rd pass)%}')
AND    dl2.material <> dl.material
AND    qc.value <> ''
AND    qc2.location = qc.location
AND    qc2.value    = qc.value
);