SQL Update, incrementing value by 1

Question

My mind has gone blank...I am missing something obvious trying to write a small script:

I have one table with various ID's:

TBL_USETHISID
nextid int

I have another table with references:

TBL_REFS
ref varchar(6)
thisdate datetime
nextid int

I want to take the ID from TBL_USETHISID and then update TBL_REFS so each row's ID is one more than the previous. I will then select the max(nextid) from TBL_REFS and update TBL_USETHISID with the highest.

I am struggling a bit with this, we have to use this instead of auto-incrememnt fields as these ID's are used across multiple tables.

Obviously I have tried:

UPDATE TBL_REFS FROM TBL_USETHISID
SET nextid = TBL_USETHISID.nextid + 1

Thanks for all your help in advance.

EDIT - Sample data:

TBLUSETHISID:

nextid
7001

TBL_REFS

ref       thisdate               nextid
0000123   2012-10-02 00:00:00        
0000124   2012-10-02 00:00:00 
0000125   2012-10-02 00:00:00 

After update:

TBL_REFS

ref       thisdate               nextid
0000123   2012-10-02 00:00:00    7001    
0000124   2012-10-02 00:00:00    7002
0000125   2012-10-02 00:00:00    7003

Then I would UPDATE TBL_USETHISID FROM TBL_REFS a set nextid = max(a.nextid)+1 to update the original table. I hope my formatting is correct, i idented like code for readability.

Answer 1

I would strongly advise you to use SEQUENCEs. That is idiomatic Ingres approach (actually, sequences are SQL:2003 standard, if I remember well, so every good RDBMS which supports SQL should support them).

Unfortunately Actian moved the documentation to a new system which makes it very difficult to create a direct link to a page describing something, so I can't really give you a link here. Please go to http://docs.actian.com , pick Ingres 10 docs (in Options), and open the SQL reference.

In short, make a sequence called, say TBL_REFS_SEQ:

CREATE SEQUENCE TBL_REFS_SEQ; -- Also grant it

Then you can do something like:

UPDATE TBL_REFS 
FROM TBL_USETHISID 
SET nextid = TBL_REFS_SEQ.NEXTVAL;

Sequence also has the CURVAL property.

NOTE: Keep in mind that you need to grant privileges to this newly created sequence if you want it to be accessable by certain users/roles.

From the Ingres 10.0 SQL Reference Guide:

A NEXT VALUE or CURRENT VALUE expression on a particular sequence is evaluated once per row inserted by an INSERT statement, updated by an UPDATE statement, or added to the result set of a SELECT statement. If several occurrences of a NEXT VALUE or CURRENT VALUE expression on the same sequence are coded in a single statement, only one value is computed for each row touched by the statement. If a NEXT VALUE expression and a CURRENT VALUE expression are coded on the same sequence in the same statement, the NEXT VALUE expression is evaluated first, then the CURRENT VALUE expression (assuring they return the same value), regardless of their order in the statement syntax.

Answer 2

try this,

UPDATE tbl_refs  SET nextid=(SELECT nextid+1 FROM tbl_usethisid)

Answer 3

The most simplest way is through LOOPS although not the most efficient way. Also not sure about the syntax in ingres as I have never worked on it.

It is also possible through a CTE (again not sure if CTEs work in ingres). Will try and give a solution for that as well.

Till then Check the below code

--simulated table structure
DECLARE @TBLUSETHISID TABLE
(
    nextid INT
)

DECLARE @TBL_REFS TABLE
(
    ref varchar(6),
    thisdate datetime,
    nextid int
)

-- values for testing
INSERT INTO @TBLUSETHISID VALUES(7001);

INSERT INTO @TBL_REFS VALUES('000123', '2012-10-02 00:00:00', null);
INSERT INTO @TBL_REFS VALUES('000124', '2012-10-02 00:00:00', null);
INSERT INTO @TBL_REFS VALUES('000125', '2012-10-02 00:00:00', null);

--solution starts from here
DECLARE @StartCount INT, @TotalCount INT, @REF VARCHAR(6)

SELECT @TotalCount = COUNT(*) - 1 FROM @TBL_REFS;
SET @StartCount = 0;

WHILE(@StartCount <= @TotalCount)
BEGIN
    SELECT @REF = ref FROM (SELECT ROW_NUMBER() over(ORDER BY ref) AS ROWNUM, * FROM @TBL_REFS) as tbl WHERE ROWNUM = @StartCount + 1

    UPDATE @TBL_REFS
    SET nextid = (SELECT nextid + @StartCount FROM @TBLUSETHISID)
    WHERE ref = @REF

    SET @StartCount = @StartCount + 1

END

UPDATE @TBLUSETHISID
SET nextid = (SELECT MAX(nextid) + 1 FROM @TBL_REFS)

SELECT * FROM @TBLUSETHISID
SELECT * FROM @TBL_REFS

EDIT: A better solution than LOOP. The table simulation and test value insertion remains as per the above solution. Of course the problem of Ingres doesn't support scalar queries remains so you have to find a work around for this.

UPDATE tbl2
SET nextid = (tbl.nextid + ROWNUM - 1) 
FROM
(SELECT ROW_NUMBER() over(ORDER BY ref) AS ROWNUM, 
ref,
thisdate,
(SELECT nextid FROM @TBLUSETHISID) AS nextid FROM @TBL_REFS) tbl
INNER JOIN @TBL_REFS tbl2
ON tbl.ref = tbl2.ref

Hope this helps