Getting the Id of a row I updated in Sql Server
The @@identity
and scope_identity()
will hand you the identity of a new row, ie. after an insert. After your update, the identity of the row is... @Customer_ID or @Handle_Id? If it is a different field, you should use the OUTPUT clause to return the ID of the updated row:
UPDATE ITS2_UserNames
SET AupIp = @AupIp
OUTPUT INSERTED.PrimaryKeyID
WHERE @Customer_ID = TCID AND @Handle_ID = ID
How to get the ID of last updated Row in SQL Server 2008
You can do something like this
declare @mytable as TABLE
(
Id int
)
Update Table Set Name='Nitin'
OUTPUT INSERTED.Id into @mytable
where LastName='Varpe'
Select Id from @mytable
SQL Server - Best way to get identity of inserted row?
@@IDENTITY
returns the last identity value generated for any table in the current session, across all scopes. You need to be careful here, since it's across scopes. You could get a value from a trigger, instead of your current statement.SCOPE_IDENTITY()
returns the last identity value generated for any table in the current session and the current scope. Generally what you want to use.IDENT_CURRENT('tableName')
returns the last identity value generated for a specific table in any session and any scope. This lets you specify which table you want the value from, in case the two above aren't quite what you need (very rare). Also, as @Guy Starbuck mentioned, "You could use this if you want to get the current IDENTITY value for a table that you have not inserted a record into."The
OUTPUT
clause of theINSERT
statement will let you access every row that was inserted via that statement. Since it's scoped to the specific statement, it's more straightforward than the other functions above. However, it's a little more verbose (you'll need to insert into a table variable/temp table and then query that) and it gives results even in an error scenario where the statement is rolled back. That said, if your query uses a parallel execution plan, this is the only guaranteed method for getting the identity (short of turning off parallelism). However, it is executed before triggers and cannot be used to return trigger-generated values.
Get the Identity of Last Updated Row in SQL Server
You cannot retrieve an ID since there is no ID being inserted.....
But you can:
just query the table using the same criteria as in your
UPDATE
:SELECT TableID
FROM dbo.Table
WHERE SC = @SC AND Service = @Ser -- just use the same criteriause the
OUTPUT
clause on theUPDATE
to get that info:UPDATE [Table]
SET Active = 1,
Subscribed = 1,
RenewDate = GETDATE(),
EndDate = DATEADD(mm,1,getdate())
OUTPUT Inserted.TableId -- output the TableID from the table
WHERE SC = @SC AND Service = @Ser
Read more about the OUTPUT
clause on Technet - it can be used on INSERT
and DELETE
as well
How do I get row id of a row in sql server
SQL Server does not track the order of inserted rows, so there is no reliable way to get that information given your current table structure. Even if employee_id
is an IDENTITY
column, it is not 100% foolproof to rely on that for order of insertion (since you can fill gaps and even create duplicate ID values using SET IDENTITY_INSERT ON
). If employee_id
is an IDENTITY
column and you are sure that rows aren't manually inserted out of order, you should be able to use this variation of your query to select the data in sequence, newest first:
SELECT
ROW_NUMBER() OVER (ORDER BY EMPLOYEE_ID DESC) AS ID,
EMPLOYEE_ID,
EMPLOYEE_NAME
FROM dbo.CSBCA1_5_FPCIC_2012_EES207201222743
ORDER BY ID;
You can make a change to your table to track this information for new rows, but you won't be able to derive it for your existing data (they will all me marked as inserted at the time you make this change).
ALTER TABLE dbo.CSBCA1_5_FPCIC_2012_EES207201222743
-- wow, who named this?
ADD CreatedDate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP;
Note that this may break existing code that just does INSERT INTO dbo.whatever SELECT/VALUES()
- e.g. you may have to revisit your code and define a proper, explicit column list.
How to get IDs of my batch update SQL Server
Use the OUTPUT
clause. It provides you with a "table" named deleted
which contains the values before the update, and a "table" named inserted
which contains the new values.
So, you can run
Update tbl.myTable set Amount = 0
output inserted.*,deleted.*
where Date = '01/01/2019'
To understand how it works, succeeding this, you can now create a temporary table and OUTPUT
the fields you want INTO
it:
Update tbl.myTable set Amount = 0
output inserted.*,deleted.* into temp_table_with_updated
where Date = '01/01/2019'
How to get ID of updated row?
The documentation for insert_id clearly states:
Returns the ID generated by an INSERT or UPDATE query on a table with a column having the AUTO_INCREMENT attribute.
Your query does not generate a new ID. You can't use $db->insert_id
as there was no new ID reported by MySQL server.
You can trick MySQL into providing this value. Just reset the ID to the value that it had previously by regenerating it again.
$sql = $db->prepare("UPDATE `timeslots`
SET `service` = ?, Id=LAST_INSERT_ID(Id)
WHERE `status` = ?");
See How to get ID of the last updated row in MySQL?
SQL: Get ID of newly inserted row
You can do it as described in the link below:
How to get the id of last inserted row using preparedstatement?
your code could be:
public static void addNewPunishment(UUID playerUUID, int punishmentBlocks, int maxExtensions) {
try (final PreparedStatement ps = DatabaseConnector.getConnection().prepareStatement(
"insert into PRISONERS(UUID, REMAININGBLOCKS, STARTINGBLOCKS, MAXEXTENSIONS) values (?,?,?,?)"), Statement.RETURN_GENERATED_KEYS) {
ps.setString(1, playerUUID.toString());
ps.setInt(2, punishmentBlocks);
ps.setInt(3, punishmentBlocks);
ps.setInt(4, maxExtensions);
ps.executeUpdate();
ResultSet rs = ps.getGeneratedKeys();
if(rs.next())
{
int last_inserted_id = rs.getInt(1);
}
} catch (SQLException e) {
e.printStackTrace();
}
}
How to get updating row ID in SQL Server trigger
Try this:
ALTER TRIGGER [dbo].[EmployeeTrigger]
ON [dbo].[Employee]
FOR UPDATE
AS
IF((
SELECT COUNT([DivisionID ])
FROM [DivisionInfo] D
JOIN INSERTED I ON I.[Emp_Id] = D.[Emp_Id])>0)
BEGIN
RAISERROR ('Testing', 10, 1);
ROLLBACK TRANSACTION
RETURN
END
Related Topics
SQL on Delete Cascade, Which Way Does the Deletion Occur
How to Find Sum of Multiple Columns in a Table in SQL Server 2005
Why Don't Dbms's Support Assertion
Multiple Array_Agg() Calls in a Single Query
Move Cells Left in SQL If Left Contains Null and Right Contains Value
SQL Server Search Using Like While Ignoring Blank Spaces
Sql: Two Select Statements in One Query
How Important Are Lookup Tables
Row-Level Trigger VS Statement-Level Trigger
Getting the Id of a Row I Updated in SQL Server
Union All VS or Condition in SQL Server Query
Combine Multiple Select Statements