Postgresql update query (with a subquery) error

Question

I am getting error on this query. The first inner query is executing properly while we run it independently. But while I include the same in the complete query, its prompting the error.

UPDATE GL_BudgetPlanDetails bpd 
SET (CommittedAmt07) = (
        SELECT SUM (case
            when fa.amtacctdr > 0 then (fa.amtacctdr*-1)
            when fa.amtacctcr > 0 then fa.amtacctcr else 0 end )
        FROM fact_acct fa
        WHERE fa.datetrx between '2014-09-01' and '2014-09-30'
          AND fa.isactive ='Y'
          AND fa.account_ID =758
          AND fa.AD_Client_ID =11
          AND fa.AD_Org_ID =50001)
WHERE (
        (
            SELECT gbp.c_year_id
              FROM GL_BudgetPlan gbp
             WHERE gbp.GL_BudgetPlan_ID = bpd.GL_BudgetPlan_ID
               AND gbp.isactive ='Y'
        ) = '2014'
      )
AND bpd.account_ID =758

And the error is? (and please format the statement so that it's visible without horizontal scrolling) — user330315
– user330315, Commented Sep 18, 2014 at 13:38
"its prompting the error." That's like saying "it doesn't work". Show the exact text of the error message by editing your question to add it (do NOT paste it in a comment). Then comment here when you've made your edits. While you're at it, please include your PostgreSQL version. — Craig Ringer
– Craig Ringer, Commented Sep 18, 2014 at 14:15

sanoop · Accepted Answer · 2014-09-18 13:41:55Z

2

Remove brackets of CommittedAmt07

UPDATE GL_BudgetPlanDetails bpd SET Period01Amt=(SELECT SUM (case when fa.amtacctdr > 0 then (fa.amtacctdr*-1) when fa.amtacctcr > 0 then fa.amtacctcr else 0 end ) FROM fact_acct fa WHERE fa.datetrx between '2014-09-01' and '2014-09-30' AND fa.isactive ='Y' AND fa.account_ID =758 AND fa.AD_Client_ID =11 AND fa.AD_Org_ID =50001) WHERE ((SELECT gbp.c_year_id FROM GL_BudgetPlan gbp WHERE gbp.GL_BudgetPlan_ID = bpd.GL_BudgetPlan_ID AND gbp.isactive ='Y' ) = '2014') AND bpd.account_ID =758

answered Sep 18, 2014 at 13:41

sanoop

921 silver badge8 bronze badges

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

1 Comment

Craig Ringer Over a year ago

That might be correct, but it's kind of illegible and doesn't explain why it's correct. An edit might be warranted.

frlan · Accepted Answer · 2014-09-18 17:52:32Z

I think the issue are the () arround CommittedAmt07 so your query should look like

UPDATE GL_BudgetPlanDetails bpd 
SET CommittedAmt07 = (
        SELECT SUM (case
            when fa.amtacctdr > 0 then (fa.amtacctdr*-1)
            when fa.amtacctcr > 0 then fa.amtacctcr else 0 end )
        FROM fact_acct fa
        WHERE fa.datetrx between '2014-09-01' and '2014-09-30'
          AND fa.isactive ='Y'
          AND fa.account_ID =758
          AND fa.AD_Client_ID =11
          AND fa.AD_Org_ID =50001)
WHERE (
        (
            SELECT gbp.c_year_id
              FROM GL_BudgetPlan gbp
             WHERE gbp.GL_BudgetPlan_ID = bpd.GL_BudgetPlan_ID
               AND gbp.isactive ='Y'
        ) = '2014'
      )
AND bpd.account_ID =758;

If you are having a look onto here, you can see that there doesn't need to be brackets if you just update one column. But you will need to use them ones you update it with some kind of a array. See this example from documentation

UPDATE weather SET (temp_lo, temp_hi, prcp) = (temp_lo+1, temp_lo+15, DEFAULT)
WHERE city = 'San Francisco' AND date = '2003-07-03';

Also for 9.3 they are stating that filling it from a SELECT-statement is not yet implmeented (on the very low of the linked page)

Collectives™ on Stack Overflow

Postgresql update query (with a subquery) error

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related