Re: UPDATE - Column order guaranteed?

Will Hartung (vfr750@netcom.com) wrote: : Hi All... : Simple Q for you all. : In the following UPDATE statment fragment: : UPDATE table set col1=col2, col2=col3, col3=0 : Is this update GUARANTEED to be in the order I presented? Is there Fascinating Question! : any chance of the assignments happening out of sequence? (Is my intent : clear? Doing a sort of bucket roll here...) My money says it's not guaranteed. : Sure, it WORKS this way in my tests. : Sure, it seems logical to think that it would always work this way. : Sure, you all do it...but do you KNOW? Nope. : I can't seem to find anything in the Informix manuals that says this : behavior will ALWAYS happen. Maybe it's in the SQL standard that these Which is a good indication that it's not guaranteed but undefined. : assignments are serialized as presented, but I don't happen to have a : copy of the standard handy. Nor do I. I bet it either ducks the issue or says "implementation dependant". : For all I know, it's just a convenient side effect. The Trigger manuals : document that multiple update triggers happen in column order, but : there isn't anything anywhere that states what's going to happen here. : Maybe the updates are happening in column order as well. But which column order is it using? The one in your statement or the one in the table? The way you've phrased your update they are probably one and the same. Today, that is. What about tomorrow? In a relation, not only is the order of the tuples without significance, but also the order of the attributes. You could be asking is Informix: a) non-relational in this respect? b) guaranteed to stay non-relational in this respect? : It's seems like common sense would dicate, but we're talking computers : and Informix here. Their common sense clearly differs from mine, from : time to time. For all I know in OnLine 9.x they're going to allow table : partitioning by column as well and "subtley change some engine : behaviors". What a hoot that change would be to track down. Sounds distinctly plausible. : This is really digusting because I have ONE statement in my ninety : gazillion lines of code for this current application that does this. I : could easily serialize it myself with two updates and be done with it. : The second update would go zingy fast because everything is cached up : the ying-yang and I'm hitting one whole row. So why not play it safe? : If the assignment order changes, or is inherently undefined and JUST : HAPPENS to work all the way up until the application hits "The Busy : Season" then I get a phone call: : "Why are our numbers suddenly completely hosed?" : "Ummm...." : "We need these numbers fixed immediately." : "Well, I was kind of out the door to catch my non-refundable flight : to Hawaii..." : "And it seems the rest of the seasons numbers are also a mess..." : "I have a snorkeling reservation, too..." So why not play it safe? : So, anyway, just for the sake of curiosity, will that UPDATE ALWAYS : work as intended? Where's that crystal ball gone? : Thanx! : -- : Will Hartung - Hermosa Beach, Lower Left Coast. vfr750@netcom.com : 1990 VFR750 - VFR=Very Red "Ho, HaHa, Dodge, Parry, Spin, HA! THRUST!" : 1993 Explorer - Cage? Hell, it's a prison. -D. Duck -- graeme -- Disclaimer: The author's opinions are his own, and not necessarily those of Pyramid Technology Ltd. or Pyramid Technology Inc. --------------------------------------------------------------------------- -m------- Graeme Sargent Voice: +44 (0)252 373035 ---mmm----- Senior Database Consultant Fax : +44 (0)252 373135 -----mmmmm--- Pyramid Technology Ltd. Telex: Tell who??? -------mmmmmmm- Farnborough, Hants GU14 7PL Email: graeme@pyra.co.uk --------------------------------------------------------------------------- We have the technology. The tricky bit is learning how to use it.