FOR READ ONLY

The FOR READ ONLY clause is a useful amendment to SQL statements. It can be used in a number of ways and can differ between platforms. Specifically, the FOR READ ONLY clause can be appended to a SQL full select statement, or cursor in order to prevent changes to the data in the result set.

In the case of a full select statement, the FOR READ ONLY prevents modification of the data in the results set. This of course means that you cannot use this clause with an UPDATE or DELETE statement, as those statements call for modification of the data which is prevented by the statement.

In the case of cursors this clause can prevent the inadvertent modification of the table data where it is only desired to modify the data for output.

FOR

In an attempt to tease out something I mused upon earlier today but have none-the-less forgotten I am going to create a posting label for FOR. Whenever I run across FOR used in SQL I will attempt to document it and explain its usage. I do recall that whatever it was that I was thinking about FOR for had something to do with the limits of SQL for processing multiple near identical rows to the effect of collapsing them into single rows or groups of rows based on certain criteria.


Ultimately, I was inspired to this by a particular extract in which I wanted to select a yes for certain values and a no for certain other values, not producing multiple rows despite the fact that the criteria for selecting yes into the field is only satisfied when another previous row has satisfied the criteria for no, the law of the excluded middle seeming to neccesitate aggregation where scalaricity is more desirable.


I believe a work-around would involve introducing a MAX to the yes or no logic such that y being greater than no yes would supercede no as a scalar output to the aggregated input. None-the-less I have become fascinated (relatively) with the ODBC reserved keyword FOR.



Here is a nifty tool for checking whether a word is a reserved keyword.