How to run an UPDATE script in postgresql only if column value is NOT NULL?

Question

I found multiple solutions of running a query only if a parameter is not null, but none of them seems to fit my scenario.

I have to following UPDATE script:

DO '
BEGIN
    IF EXISTS
        (SELECT 1 FROM pg_class where relname=$J$scm_repos$J$)
    THEN
        UPDATE scm_repos repos SET token_id=(SELECT token_id FROM scm_orgs orgs WHERE repos.org_id=orgs.id);
        UPDATE scm_orgs SET token_id = NULL;
    END IF ;
END;
'

I want the the 1st UPDATE script to run only if the token_id on the scm_orgs is not null. What is the proper way doing it?

Julius Tuskenis · Accepted Answer · 2021-10-07 09:02:36Z

1

What you need is using UPDATE statement with FROM part in it:

UPDATE scm_repos AS repos SET 
  token_id= orgs.token_id
FROM 
  scm_orgs AS orgs
WHERE
  repos.org_id=orgs.id
  AND orgs.token_id IS NOT NULL

answered Oct 7, 2021 at 9:02

Julius Tuskenis

1,63010 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to run an UPDATE script in postgresql only if column value is NOT NULL?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related