write a sql block which will commit if both updates are successful else it will have to be rolled back

[arun chirappurath]

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