1

Im trying to update a table I have the same Data but with different ID's so i would like to set the ID of both communs to the lowest ID register for the results.

  UPDATE TABLENAME
      SET EXAMPLEID = LOWER(EXAMPLEID)
      WHERE
      TID = TID
      AND
      KID = KID
      AND 
      STREET = STREET

I'm getting the following error:

Msg 8102, Level 16, State 1, Line 1 Cannot update identity column 'EXAMPLEID'

EzLo
  • 13,780
  • 10
  • 33
  • 38
Rousblack
  • 57
  • 1
  • 9
  • 1
    mysql or sql-server ??? are not the same – ScaisEdge Feb 08 '19 at 09:12
  • 1
    Sample data and Expected results would be nice. Also you''ve tagged MySQL and aswell as SQL Server (2008). I *assume* you're using SQL Server, but please edit your tags for the RDBMS you're actually using. MySQL and SQL Server completely different products. – Thom A Feb 08 '19 at 09:12
  • 1
    Won't you end up with duplicates - usually undesirable. – P.Salmon Feb 08 '19 at 09:15
  • @scaisEdge Fixed, Thanks :) – Rousblack Feb 08 '19 at 09:16
  • What i get is: Msg 8102, Level 16, State 1, Line 1 Cannot update identity column 'EXAMPLEID'. – Rousblack Feb 08 '19 at 09:17
  • @Rousblack: Are you trying to updated auto generated column? – huMpty duMpty Feb 08 '19 at 09:18
  • @huMptyduMpty It's autogenerated indeed. – Rousblack Feb 08 '19 at 09:22
  • For update Identity column you need to set "SET IDENTITY_INSERT Table1 ON" – Mano Feb 08 '19 at 09:23
  • @Rousblack can you explain the desire of doing this update? This seems like a bad solution for a requirement. You shouldn't want to have more than 1 row with the same identity value. – EzLo Feb 08 '19 at 09:27
  • @Mano would you explain it a it more, UPDATE Adress_test SET IDENTITY_INSERT Adress_test ON WHERE HID = HID AND RID = RID AND Adres = Adres – Rousblack Feb 08 '19 at 09:28
  • @Rousblack For your reference :https://stackoverflow.com/questions/3164987/duplicate-values-in-identity-column – Mano Feb 08 '19 at 09:33
  • Please refer this one https://stackoverflow.com/questions/19155775/how-to-update-identity-column-in-sql-server – Mano Feb 08 '19 at 09:41

2 Answers2

0

You can't update an identity column. You may insert new records with an explicit value using IDENTITY_INSERT, but SQL Server won't let you do an update.

If you really need to do this, the only option you have is to copy the full table temporarily and recreate your final table again with the updated values. This is strongly NOT recommended:

  1. Create a copy of your table, with all related objects (indexes, constraints, etc.), but with no rows (only schema objects).

    CREATE TABLE TABLENAME_Mirror (
    ExampleID INT IDENTITY,
    TID VARCHAR(100),
    KID VARCHAR(100),
    STREET VARCHAR(100))

  2. Set IDENTITY_INSERT ON on this new table and insert the records with the updated values.

    SET IDENTITY_INSERT TABLENAME_Mirror ON

INSERT INTO TABLENAME_Mirror (
    ExampleID,
    TID,
    KID,
    STREET)
SELECT
    /*Updated values*/
FROM
    --....

SET IDENTITY_INSERT TABLENAME_Mirror OFF

  3. Drop the original table and rename the copied one to the original name:

    BEGIN TRANSACTION

    IF OBJECT_ID('dbo.TABLENAME') is not null 
        DROP TABLE dbo.TABLENAME

    EXEC sys.sp_rename 
        'dbo.TABLENAME_Mirror', 
        'TABLENAME'

COMMIT

You might need to reseed the identity with a proper value once the rows are inserted, if you want to keep the same seed as before.

EzLo
  • 13,780
  • 10
  • 33
  • 38
  • Thanks for all I'm thinking a new logic. To not only update but instead Insert the desired data from the column into another table and delete the whole column after the insert. – Rousblack Feb 08 '19 at 09:55
  • This depends on the business logic you are trying to achieve. Usually identity columns hold no business value, just a number to reference a row (and they should always be used this way). Updating such a column is of little sense to me. Maybe you can further explain the logic behind this value. – EzLo Feb 08 '19 at 10:02
0

Identity Column is generally used with Primary Key column. In your case if ExampleID is your primary key and also identity Column, You cannot have same ExampleID on two different rows.

Primary Key Column is unique for every row

On the other hand If your column is not PK but Identity Column, then SQL Server does not allow you to update Identity Key Column Value.

But there is a dirty workaround alternative for this (Not Recommended)

Derviş Kayımbaşıoğlu
  • 28,492
  • 4
  • 50
  • 72