-1

I'm creating a variable and checking if the variable matches then update a table but it is not working

tried a replacement of insert into instead of update, but update suits my requirement

CREATE OR REPLACE PROCEDURE "SP1" AS
V1 INT;
V2 INT;
BEGIN

SELECT SUM(SALES) 
INTO V1
FROM "TABLE_A";

SELECT SUM(SALES) 
INTO V2
FROM "TABLE_B";

IF V1=V2
THEN
UPDATE "TABLE_C"
SET "COLUMN_1"=1;
END IF

END "SP1";

In the above code,"COLUMN_1" of "TABLE_C" should be set to 1 where it is not happening

jarlh
  • 42,561
  • 8
  • 45
  • 63
Durgaprasad
  • 159
  • 2
  • 9
  • Apart from a missing semi-colon after `END IF` (and, I'd remove all double quotes if I were you), code looks OK. Do `TABLE_A` and `TABLE_B` contain rows? Are those sums equal? Have to be, otherwise `TABLE_C` won't be updated. Sample data might help. – Littlefoot May 22 '19 at 08:48
  • yes both the tables are same, I'm using semi colon as well. Just for understanding purpose, I have used " to denote dummy objects . With the above stated criteria as well, I'm not getting the output – Durgaprasad May 22 '19 at 09:03
  • Are you running the procedure? Or simply expecting it to work by compiling the procedure? – Kaushik Nayak May 22 '19 at 09:14
  • Yes I ran the proc and "COLUMN_1" of "TABLE_C" which is expected to have 1 post the run of store proc is giving me null – Durgaprasad May 22 '19 at 09:25
  • 1
    How did you execute it; and are you checking the table after execution in the same session, or a different session (which might not show the change because you haven't committed the change yet)? – Alex Poole May 22 '19 at 09:31

1 Answers1

0

There is a "COMMIT;" missing in your procedure after the update statement. Add it and run again. This will resolve your issue.

Thanks.

Ahsan Anwar
  • 39
  • 1
  • 1
  • 9
  • It isn't usually advisable to add transaction control (commit/rollback) inside a procedure; it's too easy to forget it's there and end up with partial transactions once that procedure is part of a large chain. The very top level process that starts the transaction and ultimately calls the procedure should decide whether to commit or rollback, as only that has all the information needed to make a safe decision. – Alex Poole May 22 '19 at 14:38
  • Hi Alex, Thanks for the update. Yes I agree with you. But here in this scenario, the decision is being taken inside the function about the update statement. So I say it's safe and fine to use COMMIT in this particular scenario. Thanks! – Ahsan Anwar May 24 '19 at 05:15