Get Updated Row
Depending on what you are doing, you may need to use the table syntax of OUTPUT
. A possibility is to specify a temporary table / table variable.
DECLARE @T TABLE
(
MyID INT NOT NULL
)
UPDATE Task
SET MyTime = GetDate(), MyUserId = @userid
OUTPUT INSERTED.MyID INTO @T
FROM (/* your FROM clause here */) Task
gbn got an edit in ahead of me that essentially says the same thing as above. I would like to add that another way to do this is to grab the ID first and then update by ID. Also, TOP 1 should almost always be used with an ORDER BY
.
-- You may need to clean up the error handling. I just wanted
-- to put something simple in to remind that it is necessary.
DECLARE @userid INT; SET @userid = /* e.g., */ 1234
BEGIN TRANSACTION
IF @@ERROR <> 0 RETURN
DECLARE @TaskID INT
SET @TaskID = (SELECT TOP 1 TaskID FROM Task WITH (UPDLOCK) ORDER BY /* e.g., */ TaskID) -- TaskID should be the PK of MyTable. Must be unique.
IF @@ERROR <> 0 BEGIN ROLLBACK TRANSACTION RETURN END
UPDATE Task
SET MyTime = GETDATE(), MyUserId = @userid
WHERE TaskID = @TaskID
COMMIT TRANSACTION
Return updated row attributes on UPDATE
Use the RETURNING
clause.
The optional
RETURNING
clause causesUPDATE
to compute and return
value(s) based on each row actually updated. Any expression using the
table's columns, and/or columns of other tables mentioned inFROM
, can
be computed. The new (post-update) values of the table's columns are used.
But typically it's smarter to use a join instead of a correlated subquery:
UPDATE t1
SET foreign_key = t2.id
FROM t2
WHERE t2.col = %s
AND t1.col = %s
RETURNING t1.*; -- or only selected columns
With your original query, if the subquery finds no row in t2
, t1
is updated anyway and t1.col
is set to NULL. Typically, you'd rather not update the row in this case, which is what my suggested query does instead.
BTW, target columns in the SET
clause cannot be table-qualified (only one table is updated anyway). The manual once more:
Do not include the table's name in the specification of a target
column — for example,UPDATE table_name SET table_name.col = 1
is invalid.
How to get only updated row from database and make app more efficient
Without seeing your logic:
Your controller can return the record:
return response(['payment' => $payment], Response::HTTP_OK);
Your axios method can observe that response and then do a replace on the index (just like you did when getting the index previously)
.then((response) => {
const { payment } = response.data;
this.items[index] = payment;
})
As long as items
was instantiated in data
as an []
then it's observable.
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
How to get value of last updated row in mysql?
Within the trigger body, the OLD and NEW keywords enable you to access columns in the rows affected by a trigger. OLD and NEW are MySQL extensions to triggers; they are not case sensitive.Trigger
DELIMITER $$
CREATE TRIGGER `database`.`tbl1_AFTER_UPDATE`
AFTER UPDATE
ON `tbl1`
FOR EACH ROW
BEGIN
INSERT INTO tbl2 (c1,c2,c3) VALUES (OLD.c1,OLD.c2,OLD.c3);
END $$
DELIMITER ;
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 can I get the INSERTED and UPDATED rows for an UPSERT operation in postgres
If you add a boolean updated column to the people
table:
ALTER TABLE people ADD COLUMN updated bool DEFAULT FALSE;
then you could identify updated rows by setting updated = TRUE
in the DO UPDATE SET
clause:
INSERT INTO people (SELECT * FROM people_update)
ON CONFLICT (name,surname)
DO UPDATE SET age = EXCLUDED.age , street = EXCLUDED.street , city = EXCLUDED.city
, postal = EXCLUDED.postal
, updated = TRUE
WHERE
(people.age,people.street,people.city,people.postal) IS DISTINCT FROM
(EXCLUDED.age,EXCLUDED.street,EXCLUDED.city,EXCLUDED.postal)
RETURNING *;
For example,
CREATE TABLE test.people (
name text
, surname text
, age float
, street text
, city text
, postal int
);
CREATE UNIQUE INDEX people_idx on people (name, surname);
ALTER TABLE people ADD COLUMN updated bool;
ALTER TABLE people ADD COLUMN prior_age float;
ALTER TABLE people ADD COLUMN prior_street text;
ALTER TABLE people ADD COLUMN prior_city text;
ALTER TABLE people ADD COLUMN prior_postal int;
INSERT INTO people (name, surname, age, street, city, postal) VALUES
('Sancho', 'Panza', 414, '1 Manchego', 'Barcelona', 01605)
, ('Oliver', 'Twist', 182, '2 Stilton', 'London', 01837)
, ('Quasi', 'Modo', 188, $$3 Rue d'Arcole$$, 'Paris' , 01831 )
;
CREATE TABLE test.people_update (
name text
, surname text
, age float
, street text
, city text
, postal int
);
INSERT INTO people_update (name, surname, age, street, city, postal) VALUES
('Sancho', 'Panza', 4140, '10 Idiazabal', 'Montserrat', 16050)
, ('Quasi', 'Modo', 1880, $$30 Champs Elysée$$ , 'Paris', 18310 )
, ('Pinocchio', 'Geppetto', 1380, '40 Nerbone', 'Florence', 18810)
;
INSERT INTO people (SELECT * FROM people_update)
ON CONFLICT (name,surname)
DO UPDATE SET
updated = TRUE
, prior_age = (CASE WHEN people.age = EXCLUDED.age THEN NULL ELSE people.age END)
, prior_street = (CASE WHEN people.street = EXCLUDED.street THEN NULL ELSE people.street END)
, prior_city = (CASE WHEN people.city = EXCLUDED.city THEN NULL ELSE people.city END)
, prior_postal = (CASE WHEN people.postal = EXCLUDED.postal THEN NULL ELSE people.postal END)
, age = EXCLUDED.age
, street = EXCLUDED.street
, city = EXCLUDED.city
, postal = EXCLUDED.postal
WHERE
(people.age,people.street,people.city,people.postal) IS DISTINCT FROM
(EXCLUDED.age,EXCLUDED.street,EXCLUDED.city,EXCLUDED.postal)
RETURNING *;
yields
| name | surname | age | street | city | postal | updated | prior_age | prior_street | prior_city | prior_postal |
|------------+----------+------+------------------+------------+--------+---------+-----------+----------------+------------+--------------|
| Sancho | Panza | 4140 | 10 Idiazabal | Montserrat | 16050 | t | 414 | 1 Manchego | Barcelona | 1605 |
| Quasi | Modo | 1880 | 30 Champs Elysée | Paris | 18310 | t | 188 | 3 Rue d'Arcole | | 1831 |
| Pinocchio | Geppetto | 1380 | 40 Nerbone | Florence | 18810 | f | | | | |
The updated
column shows the ('Sancho', 'Panza')
and ('Quasi', 'Modo')
lines have been updated, and('Pinocchio', 'Geppetto')
is a new insert.
Related Topics
Sql: Sort by Priority, But Put 0 Last
Do You Prefer Verbose Naming When It Comes to Database Columns
Sql: Retrieve Only the Records Whose Value Has Changed
How to Get Age in Years,Months and Days Using Oracle
Selecting Specific Columns Using Linq: What Gets Transferred
Does Except Execute Faster Than a Join When the Table Columns Are the Same
How to Log All Failed SQL Statements in Oracle 10G
Aggregate Adjacent Only Records with T-Sql
SQL - Conditional Where Clause
Ansi SQL Version of Select Top 1
MySQL Error 1248 (42000): Every Derived Table Must Have Its Own Alias
How to Know How Many Rows Will Be Affected Before Running a Query in Microsoft SQL Server 2008
Retrieving a Row, with Data from Key-Value Pair Table in MySQL
Entity Framework and Cross/Outer Apply
Which SQL Command How to Use to See the Structure of a Table on SQL Server