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

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?

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?

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

CodePudding user response：

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