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

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?

CodePudding user response：

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