Is there a way to INSERT a record, and update other record records in THE SAME TABLE, BASED ON A SET CRITERIA, all in ONE QUERY?
for example… I want to insert a record that contains :
RecID, Order, RecName, OtherField5, OtherField5,OtherField5,OtherField5,OtherField5
while at the same time all updating Order to Order+1 in all rows where Order> the inserted Order.
I am dealing with a MyISAM DB, and cant use transactions so I was wondering if this simple procedure could be done in ONE query so as to prevent data corruption.
also, if you are trying to do something like “updating Order to Order+1 in all rows where Order> the inserted Order” then you need to rethink what you’re doing, because sequential numbers should not be managed like that
thanks r937,
one question then, how would you represents a user-defined sequence of #s this independent of mainKey or order of insertion.
In other words this column would represent a custom retrieval order. and what i was thinking is that if a user wanted to insert a row after, say, custom position 5, any custom order >= 5 needs to be increased by 1. If there is a better method I would be glad to implement it. What would you suggest?
That’s a sort order - I would make your UI change the sort order value via code, then just update the SQL table accordingly…
how so?
if a table contains 9 record, and a user is inserting a 10th, which he wants to be ordered as the 5th record in the table when displayed. There must be a colum co contain this data for the SORT function, when the 10 records are retrieved. That the column I am trying to target IN ONE QUERY
when a new row is added somewhere within the user-defined sort order, or when an existing row is “moved” to a new position within the user-defined sort order, these actions are always accomplished by the front end application logic by designating another already existing row that the new or moved row will be inserted or moved after
maybe i didn’t say that too well, let me know if it didn’t make sense
anyhow, you would find the custom position (floating point number) of both the row before and the row after where the new row is supposed to end up, and you average these two numbers, so the new row goes half way between them
and because of the way floating point numbers work, you can (if necessary, but it would rarely occur in practice) keep halving the distance as many times as you want
this way you never have to mass-update all the rows that come after the insertion point
when a new row is added somewhere within the user-defined sort order, or when an existing row is “moved” to a new position within the user-defined sort order, these actions are always accomplished by the front end application logic
yes, it is. The user has a SELECT with the #0(none) -> the record count(say 9, in my example). The choice is to INSERT AFTER.
so currently I am accomplishing this with TWO queries. The first updates any row with the user defined order > (in my example, > 5) than the selected order, the second inserts the new row, giving it the order selected ( in my example , 5)
Remember this part is the UI to a CMS, so the DB MUST REMEMBER what order the user has selected for each article. so as to be able to use it on an output template.
Ok am going to admit your solution sounds brilliant, but so brilliant I am having problems visualizing this.
Writing the response, I think I see what you mean. how durable could this be if I had, say 10 of 1000s or records? I mean it seems like after just a few changes you would have numbers like e^10-26 or or something.
let’s say you start off your table with all new records, and you decide to assign the custom position numbers as follows –
a 1.0
b 2.0
c 3.0
x 4.0
g 5.0
n 6.0
now you insert a new row p after c
a 1.0
b 2.0
c 3.0
p 3.5
x 4.0
g 5.0
n 6.0
now you move b to after p
a 1.0
c 3.0
p 3.5
b 3.75
x 4.0
g 5.0
n 6.0
now you add a new row h after b
a 1.0
c 3.0
p 3.5
b 3.75
h 3.875
x 4.0
g 5.0
n 6.0
you can see where i’m going, yes?
how many times would you have to insert a new custom number after the latest insertion point (i.e. before 4.0) before you run out of significant digits?
okay, now imagine you started off your custom position numbers incrementing by 10000 instead of 1… now how many insertions can you do?
how many significant digits can you define for a floating point number?