Shift several columns in table
in [Queries]
|
Prev: The Microsoft Jet database engine does not recognise...
Next: aggregate calculation works in select query but not an update quer
From: Hans on 26 May 2010 09:51 Hi! Say you have a table A with fields field1, field2, field3 and field4 and I want to shift values so field1 receives the value from field2, field2 receives the value from field3 and field3 receives the value from field4 (field4 should keep it's value) will a query like Update A set field1=field2, field2=field3, field3=field4 do the job? Will the right hand side of the assignments always have the "old" value before the update? What I'm affraid of is that I set field1=field2 but in the same statement updates field2 will access use the updated value of field2 and put that into field1? When I test it seems to work as I want but I would like someone confirm this. The alternative would be to run three queries and just update one field at a time but if the right hand side of the assignment always have the "old" value then I can do the update in one query which will gain performance in my case. Regards /Hans
From: John Spencer on 26 May 2010 10:03 I believe that you can use Update A set field1=field2, field2=field3, field3=field4 and it will work. In my experience it has worked in the past, but I have not had the need to do something like this since Access 97. The need to do something like this suggests to me that you have a table design problem or are stuck with data from another source that you need to normalize. I would test it after making a backup of the table and adding a where clause to do one record. After all the database engine you are using may have been updated and the behavior changed. Update A set field1=field2, field2=field3, field3=field4 WHERE PrimaryKey = somespecificValue John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Hans wrote: > Hi! > > Say you have a table A with fields field1, field2, field3 and field4 and I > want to shift values so field1 receives the value from field2, field2 > receives the value from field3 and field3 receives the value from field4 > (field4 should keep it's value) will a query like > > Update A set field1=field2, field2=field3, field3=field4 > > do the job? Will the right hand side of the assignments always have the > "old" value before the update? What I'm affraid of is that I set > field1=field2 but in the same statement updates field2 will access use the > updated value of field2 and put that into field1? When I test it seems to > work as I want but I would like someone confirm this. > > The alternative would be to run three queries and just update one field at a > time but if the right hand side of the assignment always have the "old" > value then I can do the update in one query which will gain performance in > my case. > > Regards > /Hans > >
From: Krzysztof Naworyta on 26 May 2010 10:59 Juzer Hans <hans(a)nospam.com> napisa� | Say you have a table A with fields field1, field2, field3 and field4 | and I want to shift values so field1 receives the value from field2, | field2 receives the value from field3 and field3 receives the value | from field4 (field4 should keep it's value) will a query like | | Update A set field1=field2, field2=field3, field3=field4 | | do the job? Will the right hand side of the assignments always have the | "old" value before the update? What I'm affraid of is that I set | field1=field2 but in the same statement updates field2 will access use | the updated value of field2 and put that into field1? When I test it | seems to work as I want but I would like someone confirm this. Yes, you can. You can even replace values in two fields: Update Table1 Set Field1 = Field2 , Field2 = Field1 -- KN
From: Jeff Boyce on 26 May 2010 11:53 I'm with John ... why? You've explained "how" you're trying to solve some business need, but not what the need is. If you'll provide a bit more specific description of "what", folks here may be able to offer alternate approaches... Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "Hans" <hans(a)nospam.com> wrote in message news:uep0DqN$KHA.5168(a)TK2MSFTNGP05.phx.gbl... > Hi! > > Say you have a table A with fields field1, field2, field3 and field4 and I > want to shift values so field1 receives the value from field2, field2 > receives the value from field3 and field3 receives the value from field4 > (field4 should keep it's value) will a query like > > Update A set field1=field2, field2=field3, field3=field4 > > do the job? Will the right hand side of the assignments always have the > "old" value before the update? What I'm affraid of is that I set > field1=field2 but in the same statement updates field2 will access use the > updated value of field2 and put that into field1? When I test it seems to > work as I want but I would like someone confirm this. > > The alternative would be to run three queries and just update one field at > a time but if the right hand side of the assignment always have the "old" > value then I can do the update in one query which will gain performance in > my case. > > Regards > /Hans >
From: Bob Barrows on 26 May 2010 12:47
Hans wrote: > Hi! > > Say you have a table A with fields field1, field2, field3 and field4 > and I want to shift values so field1 receives the value from field2, > field2 receives the value from field3 and field3 receives the value > from field4 (field4 should keep it's value) will a query like > > Update A set field1=field2, field2=field3, field3=field4 > > do the job? Will the right hand side of the assignments always have > the "old" value before the update? What I'm affraid of is that I set > field1=field2 but in the same statement updates field2 will access > use the updated value of field2 and put that into field1? When I test > it seems to work as I want but I would like someone confirm this. > > The alternative would be to run three queries and just update one > field at a time but if the right hand side of the assignment always > have the "old" value then I can do the update in one query which will > gain performance in my case. > It will work as you wish, but I would not bother doing it. I would rename the table "DoNotUse" and create a query with the original table's name that selects the data from DoNotUse, assigning the "correct" names to the columns using aliases. -- HTH, Bob Barrows |