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

• Leju Geevarghese

  SSC Eights!

  Points: 982

  More actions

  October 19, 2010 at 7:16 am

  #1239017

  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: 398890

  More actions

  October 19, 2010 at 7:56 am

  #1239042

  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 Roosevelt

  Author of:
  SQL Server Execution Plans
  SQL Server Query Performance Tuning

• Carl Federl

  One Orange Chip

  Points: 25384

  More actions

  October 19, 2010 at 7:59 am

  #1239043

  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