ROWID

[Tony]

Hi gurus.
I am looking for something like ROWID (column) in Oracle.

Why:
Let's have a table TABA with columns A, B
No PK !

Among others there are two rows in the table:
A B
2 3
2 3

Can you tell, how you can (with plain SQL) change the B value in the
second row to 4 ?
You might say, that there is no point in determinig if it will be the
first or the second row. But still, you want to change only one of them!

Amazingly enough if you try to use Microsoft tools to do this trick by
clicking and editing, the Query Analyzer will tell you that you are
trying to update more than one row and refuses to do that, while MS
Query will update both rows.

In Oracle you would type:
SELECT ROWID, A, B FROM TABA
Then you would decide which ROWID you want and
UPDATE ... WHERE ROWID='0101010101'

(Well infact there is another column for this in Oracle, called ROWNUM,
but it is not so funny to play with, when it comes to ORDER BY)
In this simple case you could use:
UPDATE ... WHERE A=2 AND B=3 AND ROWNUM=2

I am trying to write a general tool for bulk updating MS-SQL-server
tables in Excel, working very much like MS-Query. But the lack of ROWID
seems to make the task impossible.

Please - instead of expressing your feelings about me draging the
competition here, try to help me solve the problem.

Thanks and regards Tony.

[Greg D. Moore]

Tony wrote:
>
> Hi gurus.
> I am looking for something like ROWID (column) in Oracle.
>
> Why:
> Let's have a table TABA with columns A, B
> No PK !
>

I guess my question is, "why not?"

That would solve your problems.

[Tony]

Hi Greg.
Thank you for your exhausting answer though I am not sure I am interpretting
you well.
Could you, please, be more specific as to what exactly did you have in mind
would solve my problems.

Thanks Tony.

[Greg D. Moore]

Tony wrote:
>
> Hi Greg.
> Thank you for your exhausting answer though I am not sure I am interpretting
> you well.
> Could you, please, be more specific as to what exactly did you have in mind
> would solve my problems.
>

Why not have a primary key?

Rereading your question I'm guessing you're not familiar with the
Identity type in MS-SQL Server.

Add a column to your table, make it integer and then mark it as an
Identity column.

You can do this from SQL Enterprise Manager or using transact SQL (I've
got to run, otherwise I'd try to script it up for you, but look under
ALTER TABLE.)

This would give you the equivalent of a ROWID and then you could select
individual rows based on it.

[Tony]

Thanks Greg, now I understand.
The thing is, that I am not (always) having the priviledge of altering the table.
Also I want to write a general tool for updating the rows in any(!) table.
Imagine trying to create a new IDENTITY column to a table with some millions of
rows just becouse I want to update a few rows.

Thanks for taking your time

[Greg D. Moore]

Tony wrote:
>
> Thanks Greg, now I understand.
> The thing is, that I am not (always) having the priviledge of altering the table.
> Also I want to write a general tool for updating the rows in any(!) table.
> Imagine trying to create a new IDENTITY column to a table with some millions of
> rows just becouse I want to update a few rows.
>

Not a problem.

I realize politics often gets in the way of practicality, but I would
where possible insist on a IDENTITY column. Both for this and other
potential problems. (For example, if you ever want to do replication, a
primary key is a requirement and an Identity column is one way of
handling that.)

Of course this assumes a lot of things which aren't always possible in
the "real" world.

Good luck.

[voll...@my-deja.com]

Still not sure I understand why you want to update the second one, but
I think you could use SET ROWCOUNT or TOP to update only 1 of these
rows.

Jon Baker
In article <3A0C2353...@Lords.com>,

Tony <To...@Lords.com> wrote:
> Hi gurus.
> I am looking for something like ROWID (column) in Oracle.
>
> Why:
> Let's have a table TABA with columns A, B
> No PK !
>

Sent via Deja.com http://www.deja.com/
Before you buy.