SQLite有光标吗？ [英] Does SQLite have Cursors?

问题描述

我想知道我是否可以在SQLite中运行以下过程：

i wonder if i could run the following procedure in SQLite:

set nocount on

select T.ID, max(T.SerialNo) as SerialNo
into #Tmp_Ticket_ID
from Ticket as T, Ticket as inserted
where t.ID = inserted.ID
group by T.id having count(*) > 1

declare zeiger cursor for
    select SerialNo
    from #Tmp_Ticket_ID

declare @SerialNo int

OPEN Zeiger  
FETCH NEXT FROM zeiger INTO @SerialNo 
WHILE (@@fetch_status <> -1)  
BEGIN  
    IF (@@fetch_status <> -2)  
    BEGIN  
        update T
            set ID = (select max(id) + 1 from Ticket)
        from ticket AS T, #Tmp_Ticket_ID as I
        where t.serialNo = i.serialno
        and I.Serialno = @SerialNo
    END
    FETCH NEXT FROM zeiger INTO @SerialNo
END  
CLOSE Zeiger  
DEALLOCATE Zeiger  
DROP TABLE #Tmp_Ticket_ID

这是ms-sql2000的一个小过程，它清除给定表中Ticket_id的双倍以下结构体的票证：

This is a little procedure from a ms-sql2000 which cleans doubles of Ticket_id's in a given table Ticket of the following structur:

create table Ticket (serialNo int identity(1,1) not null
    , ID as int not null
    , Ticket_issue as varchar(50)
    , some_more_field varchar(500))

由于来自不同数据库的简单合并，ticket_id变得不唯一。为了解决这个问题，我开发了这个过程，但现在我们在SQLite-db上有一个类似的问题。

Due to a simple merger from different databases, the ticket_id's becomes not unique. To fix by renumbering this i developed this procedure but now we have a similar issue on a SQLite-db.

推荐答案

delete from Ticket
where exists
 (select rowid from Ticket t2
   where t2.ID = Ticket.ID and t2.rowid < Ticket.rowid)

rowid 是始终存在的SQLite btree索引列。

rowid is the always-present SQLite btree index column.

感谢Martin Engelschalk在2009-08-17的SQLite邮寄名单上。

Thanks to Martin Engelschalk on the SQLite mailing list on 2009-08-17.

这篇关于SQLite有光标吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！