FreeTDS Development Group

["Lowden, James K" <LowdenJK AT bernstein.com>]

Title: [freetds] Re: [JDBC] setObject() - not implemented -- I need to read questions...

Henry,

Since your question regards SQL rather than TDS, I can answer it. 

There isn't any standard way AFAIK to do what you want (and I'm not sure how standard cursors are, either).  On both Sybase and Microsoft servers, though:

set rowcount 1
update X set B=6 WHERE A=1 AND B=2 AND C=3
set rowcount 0

will do the trick as long as no triggers are involved. 

Another way, a lot more data schlepping, and a little more standard (?), is to differentiate the rows somehow, as in:

create table #X ( id int IDENTITY, A int, B int, C int )
insert #X (A, B, C) select * from X WHERE A=1 AND B=2 AND C=3
delete X WHERE A=1 AND B=2 AND C=3
update #X set B=6 WHERE id = (select min(id) from #X)
insert X select A, B, C from #X
drop table #X

You don't have to use identities, of course; any unique value (such as timestamp) will do the job.

--jkl

-----Original Message-----
Here is an example:

In SQL Server, lets say we have a table, X:

A       B       C
_____ _____ _______
1     2     3
4     5     6
1     2     3
9     9     9

Here we have two rows where (A, B, C) = (1, 2, 3). If I'm reading through
the table and get to the third record and want to do rs.setObject(6) on B,
then how will I identify that I want only the third record changed and not
the first one from the standpoint of the freetds driver? The only way I can
see to do this is by using a cursor for navigation. Without a cursor, there
is no way to differentiate between the first and third row.

If I want to update without a cursor:

UPDATE X set B=6 WHERE A=1 AND B=2 AND C=3 --will change records 1 and 3.