0

lets say the table name is abc and **columns are name varchar(20) and status varchar(1),**status can be either 'y' or 'n' on insertion of each new row the status value of the new row must be set to 'y' and the status value of previous row has to be set to 'n'. Please guide me with a solution for this problem

lakshmi chandhana
  • 1
  • 1

1 Answers1

0

Since you are using pl/sql, you can fire a pre update statement. So before loading into the table, issue this

sql_stmnt= 'UPDATE table SET status=''n'' WHERE status =''y''';
EXECUTE IMMEDIATE sql_stmnt;
commit;
EXECUTE IMMEDIATE 'INSERT INTO...'
Koushik Roy
  • 6,868
  • 2
  • 12
  • 33
  • The approach @KoushikRoy outlines is valid but a couple comments. First, **do not commit after the update. Apparently your table must have exactly 1 row with status 'y', so what happens when an error occurs during the Insert. Your database is now corrupted as there is no 'Y' status. This is a basic transaction and both statements need to succeed or neither is committed. Second, there seems no reason for dynamic SQL. Always prefer static SQL if it is available. – Belayer Mar 04 '21 at 18:30