EasyCFM.COM - The ColdFusion Community! About Us!

New Member
Posts: 35
Joined: Mar 2003

Hi,

In my database I have a table which holds all my lookup values. Here is an example of what is stored in the table:

ID   Label    SortOrder  Table
125  A        1          1
126  B        2          1
127  D        3          1
128  E        4          1

Now I want to insert a new lookup value for C with the SortOrder of 3. After I insert the value I want the table to look like so:

ID   Label    SortOrder  Table
125  A        1          1
126  B        2          1
129  C        3          1
127  D        4          1
128  E        5          1

That is, all lookup values greater or equal to the inserted sort order (rows D and E) are incremented by one.

Can someone help me with the code to do this in ColdFusion?

Thanks in advance

This message was edited by malk on 11-11-04 @ 9:11 PM

falconseye

11-12-2004 @ 9:37 AM

Senior Member
Posts: 1033
Joined: Mar 2004

i am sorry, but isn't there a better way to do this?

11-12-2004 @ 10:10 AM

Administrator
Posts: 4262
Joined: Oct 2002

all i can think of is two queries...

UPDATE table
SET
sortOrder = sortOrder + 1
WHERE
sortOrder >= 3

INSERT into TABLE (Label, SortOrder, Table)
VALUES ('C', 3, 1)

-CJ-
@ #coldfusion/DALNet
http://charlie.griefer.com

malk

11-18-2004 @ 11:24 PM