How to use From clause inside PostgreSQL Update statement

Question

I am converting SQL stored procedure to PostgreSQL stored function. In this stored function inside Update there is From clause.

I want to know how to use From inside Update? Because I am getting error table name "account" specified more than once

CREATE OR REPLACE FUNCTION ETL_Insert_ModifyData( instID numeric)

  RETURNS void
  LANGUAGE 'sql'

AS $$ 

        --Account
        UPDATE account
        SET  namount = stg_account.namount, 
             slocation = stg_account.sLocation, 
             ndept_id = stg_account.ndept_id ,
             account.naccount_cpc_mapping_id = stg_account.naccount_cpc_mapping_id
        FROM account 
            INNER JOIN stg_account ON account.naccount_id = stg_account.naccount_id
            INNER JOIN department ON stg_account.ndept_id = department.ndept_id
            INNER JOIN accountcpcmapping ON stg_account.naccount_cpc_mapping_id = accountcpcmapping.naccount_cpc_mapping_id

        WHERE account.ninst_id = instID
            AND department.ninst_id = instID
            AND accountcpcmapping.ninst_id = instID

        --print 'Account completed '    

    $$

Kaushik Nayak · Accepted Answer · 2019-04-22 14:35:55Z

1

Move the JOIN condition with account table and stg_account to WHERE clause. Also, you need not refer to account in the SET.

Further, prefer shorter aliases (like one or 2 letters) rather than using complete table names.

UPDATE account
SET
    namount   = stg_account.namount,
    slocation = stg_account.sLocation,
    ndept_id  = stg_account.ndept_id ,
    naccount_cpc_mapping_id = stg_account.naccount_cpc_mapping_id
FROM stg_account
INNER JOIN department ON stg_account.ndept_id = department.ndept_id
INNER JOIN accountcpcmapping ON
    stg_account.naccount_cpc_mapping_id = accountcpcmapping.naccount_cpc_mapping_id
WHERE account.naccount_id   = stg_account.naccount_id
    AND account.ninst_id    = instID
    AND department.ninst_id = instID
    AND accountcpcmapping.ninst_id = instID

answered Apr 22, 2019 at 14:35

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

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

Collectives™ on Stack Overflow

How to use From clause inside PostgreSQL Update statement

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