From: | arun chirappurath <arunsnmimt(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | write a sql block which will commit if both updates are successful else it will have to be rolled back |
Date: | 2023-12-07 19:37:24 |
Message-ID: | CAA23SdvyDZNZxdUH2XBVyncdc245yo4WA7Sz3j3=jhvQbNA45g@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi All,
Can someone guide me to "write a sql block which will commit if both
updates are successful else it will have to be rolled back"?
would like to explicitly specify both commit and rollback in code..
I would like to turn off the autocommit then execute the query.
Below is a just a starter ...it doesnt has COMMIT clause..
DO $$
DECLARE
emp_id1 INT := 1; -- Assuming employee ID for the first update
new_salary1 NUMERIC := 1; -- New salary for the first update
emp_id2 INT := 2; -- Assuming employee ID for the second update
new_salary2 NUMERIC := 3; -- New salary for the second update
BEGIN
-- Update Statement 1
UPDATE employees
SET salary = new_salary1
WHERE employee_id = emp_id1;
-- Update Statement 2
UPDATE employees
SET salary = new_salary2
WHERE employee_id = emp_id2;
EXCEPTION
WHEN OTHERS THEN
-- An error occurred during the update, log the error
RAISE NOTICE 'Error during updates: %', SQLERRM;
-- Roll back the transaction
ROLLBACK;
END $$;
select * from public.employees
Thanks,
Arun
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2023-12-07 19:52:45 | Re: write a sql block which will commit if both updates are successful else it will have to be rolled back |
Previous Message | Joe Conway | 2023-12-07 15:07:59 | Re: Emitting JSON to file using COPY TO |