CODEWITHSUNDEEP
sqlsql-servert-sql
crenshaw-dev
crenshaw-dev

Reputation: 8382

How to increment a secondary sequence number in a `delete from ... into` query?

Suppose I'm a budding Pokemon card collector, and I use a SQL database to keep track of my cards. I assign each kind of Pokemon a number, id. I want to keep track of the order I collected each kind, so I also assign each card a sequence number seqno. I display the cards on a shelf.

create table shelf (
    kind_id int not null,
    seqno int not null
)

insert into shelf values (0, 1), 
                         (0, 2),
                         (0, 3),
                         (1, 1),
                         (1, 2)

I've only caught two kinds of Pokemon: three of the first kind and two of the second.

When I go collect more cards, I throw them in a bag. 

create table bag (
    kind_id int not null,
)

insert into bag values (0),
                       (0),
                       (1),
                       (2)  -- woot, caught a new one

It's easy to move all my cards from the bag to the shelf...

delete from bag
output deleted.kind_id, -1
into shelf (kind_id, seqno)
select * from bag

...and then use -1 to remind me to set the sequence number, but I'm tired of setting it manually.

I'm also a very type-A coder, and I don't want to write a second query to update the sequence numbers. I want it all to happen within the "move from bag to shelf" query.

The constraints sound odd, I know. I'm trying to distill a more complex problem, so hopefully the story helps.

Upvotes: 1

Views: 190

Answers (2)

Aaron Dietz
Aaron Dietz

Reputation: 10277

Change your delete-into to an insert then delete. Use a subquery to find the MAX(seqnum) from shelf (correlated to the correct id) and increment it during the insert. Wrap the subquery in ISNULL('',1) to start the seqnum at 1 for new cards, (or use a default value constraint on the seqnum column):

SELECT b.kind_id, 
       ISNULL(seqno + ROW_NUMBER() OVER (PARTITION BY b.kind_id ORDER BY b.kind_id),1)
FROM bag b
LEFT JOIN (SELECT MAX(seqno) seqno, kind_id
           FROM shelf
           GROUP BY kind_id) s on s.kind_id = b.kind_id

Then just DELETE FROM bag

Upvotes: 2

paparazzo
paparazzo

Reputation: 45096

windows function 

select id 
     , row_number over (partition by id, order by id) as autoSeqno 
from shelf

or just use bag for the base table and don't delete it

Upvotes: 0

Related Questions