CODEWITHSUNDEEP
sqlms-accessoledb
NeilMonday
NeilMonday

Reputation: 2730

Shifting primary keys of several rows at once

I have a table titled KEYS that looks like the following. The id and version columns are both primary keys.

||  id* |  version* |  value  ||
--------------------------------
||  1   |     1     | "asdf"  ||
||  1   |     2     | "jkl;"  ||
||  1   |     3     | "qwerty"||
--------------------------------

I want to insert a row in between version 1 and 2 so that it will look like so:

||  id* |  version* |  value  ||
--------------------------------
||  1   |     1     | "asdf"  ||
||  1   |     2     | "zxcv"  ||  <--
||  1   |     3     | "jkl;"  ||
||  1   |     4     | "qwerty"||
--------------------------------

Notice that the "jkl;" and "qwerty" rows have had their version value incremented by 1. I have tried the following query to increment the version numbers by 1, however, the query errors because version 2 cannot be incremented since it will conflict with the version 3:

UPDATE KEYS SET version=version+1 WHERE id = 1 AND version >= 2

Does anyone have a way that I can insert my new row while incrementing the other rows's versions?

Note: I am connecting to an MS Access database via an OleDB driver from a c++ application.

Upvotes: 0

Views: 50

Answers (2)

Linger
Linger

Reputation: 15058

No you really are going to have to just use two separate statements:

UPDATE KEYS SET version = version + 1 WHERE id = 1 AND version >= 2

And then execute:

INSERT INTO KEYS (id, version, value) VALUES (1, 2, 'zxcv')

If you can change your database structure I would recommend using a single primary key field that has no value other than providing a relationship to other tables. Composite keys can cause serious slow down in MS Access when working with large amounts of data.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269923

This is yet another good reason to use anonymous primary keys, rather than composite primary keys. But, that is your structure.

One way is to reassign id to a non-sensical value and then assign back:

UPDATE KEYS
    SET id = -1, version = version + 1
    WHERE id = 1 AND version >= 2;

UPDATE KEYS
    SET id = 1
    WHERE id = -1;

Two updates for the price of one!

Upvotes: 3

Related Questions