Update query!

  • Thread starter Thread starter John Reynolds
  • Start date Start date
J

John Reynolds

I have a table with a field that needs to be updated.

I have the following update query:

UPDATE
SET Field1 = '5MH00'
WHERE Field1 = '5MH';

This works fine, but how do I get the update query to
update Field1 with various updates ie.

FROM TO
5MG 5MG00
5MT 5MT00
465 457

Do I have to create seperate update queries or can I merge
all these updates into one update query?
 
Hi,


Yep. Make a table

Translate ' table name
Was ShouldBe ' fields name
5MG 5MG00
5MT 5MT00
465 467 ' data



Then, after having make a backup (or a copy) of the real data:


UPDATE table1 INNER JOIN Translate ON table1.field1=translate.was
SET table1.field1=translate.ShouldBe



Hoping it may help,
Vanderghast, Access MVP
 
That's great, worked a treat. Thanks for your help.

-----Original Message-----
Hi,


Yep. Make a table

Translate ' table name
Was ShouldBe ' fields name
5MG 5MG00
5MT 5MT00
465 467 ' data



Then, after having make a backup (or a copy) of the real data:


UPDATE table1 INNER JOIN Translate ON table1.field1=translate.was
SET table1.field1=translate.ShouldBe



Hoping it may help,
Vanderghast, Access MVP


I have a table with a field that needs to be updated.

I have the following update query:

UPDATE
SET Field1 = '5MH00'
WHERE Field1 = '5MH';

This works fine, but how do I get the update query to
update Field1 with various updates ie.

FROM TO
5MG 5MG00
5MT 5MT00
465 457

Do I have to create seperate update queries or can I merge
all these updates into one update query?
Click to expand...



.
Click to expand...
 
Dear John:

If this is a one time requirement you would probably be well off to
just create the 3 update queries and be done. But if that were the
case you probably wouldn't have posted this question.

An alternative for repeated updates would be to create a table of your
FROM / TO values and write an update that joins to this table. This
would allow users to create the mapping of old to new values, review
it, report it, etc. before applying it.

Notice that it may make a difference in what order the updates are
applied. Logically A -> B and B -> C might result in your table
having rows with B in them where there used to be A. But if the B ->
C is logically applied after the A -> B change then you would have C
where there was originally A. Some thought along these lines,
including what the users might be expecting, would be a good thing
before programming this.

I have a table with a field that needs to be updated.

I have the following update query:

UPDATE
SET Field1 = '5MH00'
WHERE Field1 = '5MH';

This works fine, but how do I get the update query to
update Field1 with various updates ie.

FROM TO
5MG 5MG00
5MT 5MT00
465 457

Do I have to create seperate update queries or can I merge
all these updates into one update query?
Click to expand...


Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top