How to get the Internal ID from a ROW

Question

Post reply

How to get the Internal ID from a ROW

guido.birkenfeld

SSC Veteran

Points: 213
More actions
October 19, 2010 at 7:08 am

#228200

Hello together,
i have a table like this:
ID PID
1 3
1 3
and I would like to update the first row and set PID to 4
but i can't make it with a update statement.
if I do this in an Database Managing tool i can do it by klicking in and chang the value.
My Question is:
How can i get the internel ID from the row which is used by the Databasetool to update the Row?
Thanks in advance

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Leju Geevarghese SSC Eights! Points: 982 More actions · Answer 1

check this article out

http://www.databasejournal.com/features/mssql/article.php/3572301/RowNumber-function-in-SQL-Server-2005.htm

---------------------------------------------------------------------------------------
It begins by taking the first step.

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 2

This is not something I've tried before. When I tried to replicate what you said happens, with duplicate rows and editing them using SSMS, I got this error:

This is a very, very bad approach to doing things. You should have a unique constraint of some kind on the table. But, here's one way it works. You'll have to get the physical location, and then you can use that to do the update:

SELECT sys.fn_PhysLocFormatter(%%physloc%%) AS RID, *

FROM dbo.MyTable

UPDATE dbo.MyTable

SET Column = 'NewValue'

WHERE sys.fn_PhysLocFormatter(%%physloc%%) = '(1:155:1)'

I had to go out to Twitter to track this down. Thanks to @MladenPrajdic & @AdamMachanic.

You really should not be doing this, just so you know.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Carl Federl One Orange Chip Points: 25384 More actions · Answer 3

I do this in an Database Managing tool i can do it by klicking in and chang the value.

Are you sure? When I try to use SSMS to update a row without a uniqueness constraint, I get this message:

No row was updated.

The data in row 1 was not committed.

Error Source: Microsoft.SqlServer.Management.DataTools.

Error Message: The row value(s) updated or deleted either do not make the row unique or they alter multiple rows(2 rows).

Correct the errors and retry or press ESC to cancel the change(s).

use tempdb

go

create table Duplicates

(C1integernot null

,C2 integer not null

)

go

insert into Duplicates

(c1,c2)

select top 100 *

from(

selectobject_id, column_id

fromsys.all_columns

union all

selectobject_id, column_id

fromsys.all_columns

) as x

order by object_id, column_id

;

-- drop table Duplicates

SQL = Scarcely Qualifies as a Language