Is it possible to use SCOPE_IDENTITY in an update? Alternatives?

Question

Is it possible to use SCOPE_IDENTITY in an update? Alternatives?

Navigation

#1 by (1 votes)

1

Use SCOPE_IDENTITY to recover the id of the insertion and thus be able to work with it. Therefore I know that with insert it works.

The question is, does it work with the update ? I have tried to follow a similar process and it returns null.

In case it does not work, any way to do it? Do I have to declare variables and set them?

Example of a non-functional update:

UPDATE
       [XXX].[dbo].[FirmanteActaModeloGeneral]
Set    [Tratamiento]               = 'D.'
     , [NombreFirmante]            = 'NombreFirmante'
     , [Apellido1Firmante]         = 'Apellido1Firmante'
     , [Apellido2Firmante]         = 'Apellido2Firmante'
     , [CargoFirmante]             = 'CargoFirmante'
     , [PosicionFirmaActaAraba]    = 0
     , [PosicionFirmaActaBizkaia]  = 0
     , [PosicionFirmaActaGipuzkoa] = 0
WHERE
       IdFirmante =30;

SELECT SCOPE_IDENTITY();

sql sql-server

asked by GDP 07.06.2018 в 10:05

source

1 answer

Maintain hover effect when selected add a constant to a string -php

score 1 · Answer 1

According to the documentation : Returns the last identity value inserted into an identity column in the same scope . That is, the last valor de identity inserted, does not work for the e UPDATE statements.

But, you can use the clause OUTPUT together with the UPDATE . for example:

DECLARE @UpdatedValues table(  
    IdFirmante int NOT NULL
)

UPDATE
       [XXX].[dbo].[FirmanteActaModeloGeneral]
Set    [Tratamiento]               = 'D.'
     , [NombreFirmante]            = 'NombreFirmante'
     , [Apellido1Firmante]         = 'Apellido1Firmante'
     , [Apellido2Firmante]         = 'Apellido2Firmante'
     , [CargoFirmante]             = 'CargoFirmante'
     , [PosicionFirmaActaAraba]    = 0
     , [PosicionFirmaActaBizkaia]  = 0
     , [PosicionFirmaActaGipuzkoa] = 0
WHERE
       IdFirmante =30
OUTPUT inserted.IdFirmante  
       INTO @UpdatedValues;  

SELECT * FROM UpdatedValues;

Detail:

The OUTPUT allows you to register any previous value deleted or later inserted
We can, if we search for it, register the entire row indicating all the columns
We could also use a physical table to record the values.
I'm assuming that IdFirmante is identity .