EasyCFM.COM ColdFusion Forums / Coding Help! / insert value and update sort orders

   Reply to Discussion | New Discussion << previous || next >> 
Posted By Discussion Topic: insert value and update sort orders

book mark this topic Printer-friendly Version  send this discussion to a friend  new posts last

malk
11-11-2004 @ 9:11 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
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
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Senior Member
Posts: 1033
Joined: Mar 2004

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

CJ
11-12-2004 @ 10:10 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
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
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
New Member
Posts: 35
Joined: Mar 2003

Thanks CJ - that worked great!

I thought I'd have to loop over the query doing freaky things, but as you pointed out I just needed a couple of queries.

Thanks heaps Smile

Sponsored By...
iOpenSoft, LLC is a Houston, Texas Advanced Technology Studio Specializing in Web Design, Web Development, iPhone App Development and Android App Development.