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;

Have you tried the oldschool `UPDATE a,b,c SET a.x = 1 WHERE a.y = b.z AND b.id = c.id AND c.type = "Beton"` type of UPDATE which joins several tables ? — IVO GELOV, Apr 30 '22 at 08:09
Share table schema with some data so it would be helpful to solve the issue. — Almas Mahfooz, May 20 '22 at 11:48

Erwin Brandstetter · Answer 1 · 2022-05-17T22:27:19.200

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.

material and location are coming from different tables. Which I forgot to mention. My bad — Puzzlemaster, May 17 '22 at 04:35
Next time please start a new question. Don't change the nature of a question after answers have been given. — Erwin Brandstetter, May 17 '22 at 21:45

score 1 · Answer 2 · answered May 24 '22 at 07:35

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

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). — Community, May 24 '22 at 11:16

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

2 Answers2