UPDATE rows only where a matching row exists in the same table

Question

I am trying to update a table named qc only when first.value is equal to second.value.

An UPDATE query without FROM updates every value which is not what I want. I tried this, but it's still not working:

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
);

Why Beton_v% vs. Beton%? Assuming a typo, going with the more restrictive pattern. The character _ has special meaning in a LIKE pattern. I escaped it to make it a literal _. See:

• Escape function for regular expression or LIKE patterns

Both LEFT JOIN in your original query are fake and can be replaced with INNER JOIN. See:

• Postgres LEFT JOIN with WHERE condition

This query only updates rows where at least one sibling row exists with identical (location, value), but different material - being LIKE one of ('Beton\_v%', '%(3rd pass)%').

If there are more rows like that, all of them are updated (and possibly repeatedly). That would probably be a data error and / or we'd use a different query. I can only speculate as there is no information.

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
);