根据行数插入查询

I want a query to insert a row into a table I know it is simple but the scenario is the table should not have more than 5 rows. If table has more than five rows I need to remove the old row(Or replace with new row ) (Based on the insert time stamp) then i need to insert a new row.If number of rows less than count 5 then i can directly insert a row.

我想要一个查询在表中插入一行我知道它很简单,但场景是表不应该超过5行。如果表有超过五行我需要删除旧行(或替换为新行)(基于插入时间戳)然后我需要插入一个新行。如果行数少于计数5那么我可以直接插入一行。

Please share me the query.

请与我分享查询。

4 个解决方案

#1

How about something like this.

这样的事情怎么样?

declare @count int

SELECT @count=COUNT(*)
from EP_ANSWERS

IF (@count<5)
// DO your insert here
ELSE
DELETE FROM TABLE
 WHERE inserttimestamp = (SELECT x.inserttimestamp
                         FROM (SELECT MAX(t.inserttimestamp) AS inserttimestamp
                                 FROM TABLE t) x)
// DO your insert here

#2

If it is impossible for the table to have more than 5 rows:

如果表不可能有超过5行:

DELETE FROM yourtable
 WHERE 5 <= (SELECT COUNT(*) FROM yourtable)
   AND yourtimestamp = (SELECT MIN(yourtimestamp) FROM yourtable)
;

INSERT INTO yourtable ...
;

If it is possible for the table to have more than 5 rows:

如果表有可能超过5行:

DELETE FROM yourtable
 WHERE 5 <= (SELECT COUNT(*) FROM yourtable)
   AND yourtimestamp NOT IN (SELECT yourtimestamp
                               FROM yourtable
                              ORDER BY yourtimestamp DESC
                              LIMIT 4)
;

INSERT INTO yourtable ...
;

#3

It sounds like you want to put a trigger on the table to maintain this rule, in MySQL the something like this should work

听起来你想在表上放一个触发器来维护这个规则,在MySQL中这样的东西应该可行

CREATE TRIGGER trg__my_table__limit_rows
BEFORE INSERT
ON my_table
FOR EACH ROW 
BEGIN
    IF ((SELECT COUNT(1) FROM my_table) = 5)
    BEGIN            
        DELETE FROM my_table
        WHERE id = (SELECT MIN(id) FROM my_table)  -- change this to fit your logic for which record should be removed
    END
END

#4

Some of the code here is in pseudo (you didn't wrote your schema), but i wrote where you need to complete your own code.

这里的一些代码是伪的(你没有编写你的模式),但我写了你需要完成自己的代码。

DECLARE @NumberOfRowsToInsert INT = -- select from the data you want to insert
DECLARE @MaxNumberOfRows INT = 5
DECLARE @NumberOfExistingRows INT

DECLARE @Query VARCHAR(MAX) = 'SELECT TOP @rows id FROM SomeTable ORDER BY createdDate ASC'

SELECT @NumberOfExistingRows = COUNT(*)
FROM SomeTable

SET @Query = REPLACE(@Query,'@rows',
 CAST(@NumberOfRowsToInsert - (@MaxNumberOfRows - @NumberOfExistingRows))) AS VARCHAR(1))

CREATE TABLE #IdsToDelete(id INT PRIMARY KEY)

INSERT INTO #IdsToDelete
EXEC(@Query)

DELETE FROM SomeTable
WHERE id IN (SELECT * FROM #IdsToDelete)

-- insert here..

#1