Get Updated Row

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 causes UPDATE 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 in FROM, 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



Leave a reply



Submit