How to update bottom two rows of a table

Question

I have created below query to create a trigger to update the lastUpdatedTime. I need to update the bottom two rows using the trigger below.

But when I use

ORDER BY p.id DESC;

I get an error:

Incorrect syntax near the keyword 'order'

CREATE TRIGGER trgAfterUpdate12 
ON [spider3].[ProductClassModulePeriod]
FOR INSERT, UPDATE
AS
    UPDATE TOP(2) p 
    SET [lastUpdatedTime] = GETDATE()
    FROM [spider3].[ProductClassModulePeriod] AS p
    INNER JOIN inserted AS i ON p.id = i.id;

Tried using WITH but gets below error

CREATE TRIGGER trgAfterUpdate122 
ON [spider3].[ProductClassModulePeriod]
FOR INSERT, UPDATE
AS
    WITH q AS
    (
        SELECT * 
        FROM [spider3].[ProductClassModulePeriod] AS p
        INNER JOIN inserted AS i ON p.id = i.id
        ORDER BY p.primaryKey DESC
    )
    UPDATE q 
    SET [lastUpdatedTime] = GETDATE();

Error:

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

How can I update the bottom 2 rows?

where do you put "order by p.id desc" in the query, I don't see it here ? — Angel Deykov, Dec 22 '21 at 07:09
A table doesn't have a *bottom* row - you must mean the two rows with the highest / lowest key values - but which? — Stu, Dec 22 '21 at 09:59
You can use `row_number` for this purpose, I'm not sure if you can use an updatable CTE in the context of joining to the *inserted* table however, I would need to test it. — Stu, Dec 22 '21 at 10:20

sonam81 · Answer 1 · 2021-12-22T07:27:39.787

2

You can use this answer and implement it to update for top 2 rows after sorting with p.id in descending order.

Also, do you want to update the values for ones that are inserted only?

If updating from whole table, then you may use this.

ALTER TRIGGER trgAfterUpdate12 ON [spider3].[ProductClassModulePeriod]
FOR INSERT, UPDATE
AS
WITH cte AS
(
    SELECT  TOP 2 *
    FROM    [spider3].[ProductClassModulePeriod] p
    ORDER BY p.id DESC
)
UPDATE  cte
SET [lastUpdatedTime] = GETDATE()

edited Dec 22 '21 at 07:27

answered Dec 22 '21 at 07:17

sonam81

157
2
9

Not only for inserted. But for the ones that are already there too – Codeninja Dec 22 '21 at 07:19
Okay, in that case, refer to new edit. – sonam81 Dec 22 '21 at 07:28
How can i use inner join with this? – Codeninja Dec 22 '21 at 08:01
Do you mean inner join with inserted? If so, I don't see use of it if you need to update last 2 row from main table. – sonam81 Dec 22 '21 at 08:18
I need last two rows the table with inner join. After tables are joined im getting more than 2 rows. – Codeninja Dec 22 '21 at 08:21
Yes inner join with inserted – Codeninja Dec 22 '21 at 08:21

score 0 · Answer 2 · answered Dec 22 '21 at 07:21

0

I have a question: Why it's TWO rows you want to update when the trigger works, I think the number of rows in the temp table "inserted" is unknown. try if it works well:

UPDATE p 
SET [lastUpdatedTime] = GETDATE()
FROM [spider3].[ProductClassModulePeriod] AS p
INNER JOIN inserted AS i ON p.id = i.id;

answered Dec 22 '21 at 07:21

James

1

When a change is done a new row is added with latest = 1 and the previous row is set to latest = 0. Only those two rows need to be updated. The other rows with latest = 0 do not need to be updated. Using your query all the rows are getting updated which is unnecessary. – Codeninja Dec 22 '21 at 07:27
latest = 0 or 1 means ? – James Dec 22 '21 at 07:32

score 0 · Answer 3 · answered Dec 22 '21 at 11:58

ALTER TRIGGER trgAfterUpdate ON [spider3].[ProductClassModulePeriod]
FOR INSERT, UPDATE
AS
WITH t AS
(
select TOP 1 p.id, p.lastUpdatedTime, p.primaryKey FROM [spider3].[ProductClassModulePeriod] AS p
INNER JOIN inserted AS i
ON p.id = i.id
order by p.primaryKey desc
)
UPDATE t
SET [lastUpdatedTime] = GETDATE();

How to update bottom two rows of a table

3 Answers3