Shift several columns in table

  • Thread starter Thread starter Hans
  • Start date Start date
H

Hans

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
 
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
 
Juzer Hans <[email protected]> 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
 
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 said:
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.
Click to expand...


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.
 
Back
Top