SQL Server : How to update bottom two rows of a table-CodePudding

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();

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?

CodePudding user response：

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;

CodePudding user response：

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?