CODEWITHSUNDEEP
sqlpostgresqlselect
Mike Brown
Mike Brown

Reputation: 211

UPDATE statement with subselect

I created a script with the necessary condition for me. And it works properly. But I have a need to apply this script on many rows. And not only where id is 15.

UPDATE my_table SET my_field =
  (SELECT 
     CASE WHEN position('a' in my_field) = 0 THEN my_field
          WHEN position('a' in my_field) = 1 THEN substring(my_field from 1 for 5)
          WHEN position('a' in my_field) > 1 THEN substring(my_field from 2 for 5)
     END 
   FROM my_table WHERE id = 15)
WHERE id = 15

I would like to apply this script on rows where specific condition performs. For example I would like to write WHERE my_field LIKE '%ab%'. But the problem is that I have subselect. So I can apply separate script to get all the necessary id and then apply this script manually substituting the individual id from separate script. But I want to apply it automatically with a single query.

Upvotes: 0

Views: 100

Answers (1)

Andrey Morozov
Andrey Morozov

Reputation: 7979

It's easier than you think :)

Try this:

UPDATE my_table
SET my_field =
    CASE
          WHEN position('a' in my_field) = 0 THEN my_field
          WHEN position('a' in my_field) = 1 THEN substring(my_field from 1 for 5)
          WHEN position('a' in my_field) > 1 THEN substring(my_field from 2 for 5)
    END

Upvotes: 1

Related Questions