Re: Does a given row exist.

On Thu, 18 Nov 2004 06:59:02 -0500, Andrew Hardy wrote: Andrew: As June and other imply, it's FAR better to also tell us what you are trying to accomplish. For example, if you are testing existence to decide whether to insert a new row or update the existing row, then unless more then 70 or 80% or the tests will result in no row found, it is MUCH faster to just try the update which will quickly fail if the record does not exist (assuming efficient indexing). Then if the UPDATE indicates the zero rows were updated (it's not an error to update no rows) you can do the insert. In the case where you know ahead of time that the vast majority of transactions will be inserts and not updates, then just try the insert and if that fails with any kind of duplicate key or unique/primary key constraint violation, update the record. The failed insert is much more expensive than a failed update so, as I said, unless the vast majority of rows do not exist already, trying the update first is fastest. In either case you save the unneccessary overhead of finding and retrieving the record first before deciding what to do. Jonathan Leffler's sqlupload utility (part of the sqlcmd package) works this way as do many update apps I've written over the years. Anyway, if this is not what you want to accomplish, then tell us what is and we'll help out. Art S. Kagel > I want to check whether a row for a given critera exists. > > the only way I know at present is, for example: > > SELECT f1, f2 FROM t WHERE f3 = 'something' And ... ... ; if (SQLCODE == 0)> { > ... > } > > if (SQLCODE == 100) > { > ... > } > > I am wondering if there is a more efficient way which avoids unnecessarily > retrieving fields f1 and f2. > > Thanks, > > Andrew H. > > > > > sending to informix-list