Update query Unique index and Order by Desc

[Guest]

I have a User requriement to dynamically enter a line number in report
definitions and existing line numbers > new line number get incremented so
that all sections order correctly. (Cobol bleah!)

I am using a continuous subform to show report sections.

tblReportSections

RptSecID PK
RptID
SectionID long
SectionDescript

"TEST",0,"00Section"
"TEST",1,"01Section"
"TEST",2,"02Section"
"TEST",3,"03Section"
"TEST",4,"04Section"

Insert new section (1) and renumber existing greater than new

"TEST",0,"00Section"
"Test",1,"New 01Section" (New Entry)
"TEST",2,"01Section"
"TEST",3,"02Section"
"TEST",4,"03Section"
"TEST",5,"04Section"

I created a unique index on RptID and SectionID. Throws correct error 3022
from continuous form in form_error. Query below will work to update except
that the rows to be updated must be ordered Descending to not throw another
3022 error.

Is there a way to order the records to be updated in descending order so
that this will work?

UPDATE tblReportSection SET tblReportSection.SectionID = [SectionID]+1
WHERE (((tblReportSection.SectionID)>=[iRptSec]) AND
((tblReportSection.RptID)=[iRptID]));

Thanks in advance

 

[John Spencer]

How about running two queries sequentially?

first query adds 1 and then set the value to negative.
UPDATE tblReportSection
SET tblReportSection.SectionID = ([SectionID] +1) * -1
WHERE tblReportSection.SectionID>=[iRptSec] AND
tblReportSection.RptID=[iRptID]

2nd query then sets the negative values to positive values
UPDATE tblReportSection
SET tblReportSection.SectionID = [SectionId] *-1
WHERE tblReportSection.SectionID<0 AND
tblReportSection.RptID=[iRptID]

 

[Guest]

John your solution works perfectly.

I actually thought I'd found a way to do this with a self join for some
reason still got the 3022 error after updating the first row of the set.

UPDATE tblReportSection AS ts INNER JOIN [SELECT tblReportSection.SectionID,
tblReportSection.RpSectID,
tblReportSection.RptID
FROM tblReportSection
ORDER BY tblReportSection.SectionID DESC]. AS x ON (ts.RptID = x.RptID) AND
(ts.RpSectID = x.RpSectID) SET ts.SectionID = ts.SectionID+1
WHERE (((x.RptID)=[iRptID]) AND ((x.SectionID)>=[iRptSecID]));

I can wrap your solution in a transaction and all is well!

Thanks!

How about running two queries sequentially?

first query adds 1 and then set the value to negative.
UPDATE tblReportSection
SET tblReportSection.SectionID = ([SectionID] +1) * -1
WHERE tblReportSection.SectionID>=[iRptSec] AND
tblReportSection.RptID=[iRptID]

2nd query then sets the negative values to positive values
UPDATE tblReportSection
SET tblReportSection.SectionID = [SectionId] *-1
WHERE tblReportSection.SectionID<0 AND
tblReportSection.RptID=[iRptID]

I have a User requriement to dynamically enter a line number in report
definitions and existing line numbers > new line number get incremented so
that all sections order correctly. (Cobol bleah!)

I am using a continuous subform to show report sections.

tblReportSections

RptSecID PK
RptID
SectionID long
SectionDescript

"TEST",0,"00Section"
"TEST",1,"01Section"
"TEST",2,"02Section"
"TEST",3,"03Section"
"TEST",4,"04Section"

Insert new section (1) and renumber existing greater than new

"TEST",0,"00Section"
"Test",1,"New 01Section" (New Entry)
"TEST",2,"01Section"
"TEST",3,"02Section"
"TEST",4,"03Section"
"TEST",5,"04Section"

I created a unique index on RptID and SectionID. Throws correct error 3022
from continuous form in form_error. Query below will work to update
except
that the rows to be updated must be ordered Descending to not throw
another
3022 error.

Is there a way to order the records to be updated in descending order so
that this will work?

UPDATE tblReportSection SET tblReportSection.SectionID = [SectionID]+1
WHERE (((tblReportSection.SectionID)>=[iRptSec]) AND
((tblReportSection.RptID)=[iRptID]));

Thanks in advance

 

[giorgio rancati]

Hi StvJston,
try this