Describes how to determine which version of SQL. How to determine the version, edition, and update level of SQL. Note This query works with any instance of SQL. Microsoft Access Update Query examples, SQL Syntax and. Modifying Field Values with Update Queries. An Update Query is an. UPDATE does not generate a.

Hi Tom, I have a very basic question about the use of SELECT FOR UPDATE cursors especially when used with DELETE statements. Could you please explain to me why someone would want to get an an exclusive lock on the rows that are to be deleted? Do we care if someone tries to update them at the same time? Also, if the result that was to be achieved with a combination of SELECT FOR UPDATE and DELETE CURRENT OF statement could be done in a single delete statement (with a sub-query) can it be error-prone in a multi-user env? Won't a simple DELETE also lock the rows thus achieving the same result?

Is it absolutely essential to use SELECT FOR UPDATE for doing updates in an environment where the same data can be updated by multiple people? What are the repercussions if we don't use SELECT FOR UPDATE for updating/deleting the data? Also, will the performance of stored procedures without SELECT FOR UPDATEs be better than the ones with simple update/delete statements as there is no additional overhead of locking rows. Thanks, Gunjeet and we said. It depends on the logic.

Oracle forms for example will issue a SELECT for UPDATE on any row you attempt to update or delete from the GUI. It does this since you read the row out at time T1 and you delete it at time T2. It wants to make sure that in the time between T1 and T2 -- no one UPDATED that row. It wants to make sure you do not delete the row without having at least looked at the modified information (that would what is commonly known as a 'lost update').

It also does this to ensure that when the time comes, the delete will proceed in a non-blocking fashion. The select for update forms puts on the row ensures o the row was not changed o the row will NOT be changed o the row is locked by you and you will be able to delete it. So, in forms the logic is something akin to: 1) read the data out with select t.*, rowid from T 2) let the user look at the data 3) when the user decides to delete the '5'th' row in the result set, forms will issue: select t.*, rowid from T where t.c1 =:block.c1 and t.c2 =:block.c2 and. And t.rowid =:block. Bigger Leaner Stronger Pdf. rowid FOR UPDATE NOWAIT; if that returns ORA-54 (resource busy), you are notified someone else has that row locked.

If that returns 0 rows, you are told that the data has been modified -- you need to requery if that returns 1 row, you have the row locked and will be able to delete it and no one else can touch it. In a stored procedure - it will depend. If you do not need to inspect the data BEFORE doing the delete, you do not need this logic in general.

If you need to inspect the data before the delete -- YOU NEED this logic (to ensure the data does not change under the covers). So, if you have coded: declare cursor c1 is select * from t where. FOR UPDATE; begin for x in c1 loop delete where current of c1; end loop; end; that would be better written as begin delete from t where.; end; OTOH, if you have coded: declare cursor c1 is select * from t where. FOR UPDATE; begin for x in c1 loop some procedural code to look at X if ( some condition based on X ) then delete where current of c1; end if; end loop; end; you really do need the select for update to ensure that no one else updates your data.