Question regarding multiple updates using SQL

[3Mark3]

Hello all..this is my first time here, seems like a very good website. I'm having a problem trying to figure out how to perform multiple updates to a table. I have several different criteria that each has it's own unique update. Here's an example

6M20/12 NEEDS TO UPDATE TO 62012
1PBAL/12 NEEDS TO UPDATE TO 1CB12

And so on. I have about 55 updates I need to make to this particular table. The sql starts like this for one update? Or am I wrong?

UPDATE Base_Table SET Base_Table.UCode = "62012"
WHERE (((Base_Table.UCode)="6M20/12"))

My question is this...How can I setup a query to update every type of criteria I need? Any help would be greatly appreciated. Thank you!

 

[lespaul]

do you have a table that relates the old code to the new (like below)?

OldCode     NewCode
6M20/12     62012
1PBAL/12    1CB12

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[3Mark3]

I don't..but I can certainly add one. Would that be the best way to approach it?

 

[PHV]

Create a mapping table, say tblMapUCode:
oldUCode (Primary Key)
newUCode

Populate the 55 rows

And now the update query:
UPDATE Base_Table B INNER JOIN tblMapUCode M ON B.UCode = M.oldUCode
SET B.UCode = M.newUCode;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[3Mark3]

Awesome....worked exactly like I had hoped. Man I hate it when I step away from access as long as I have. In any case thank you both for those VERY quick responses!

 

[lespaul]

glad to help!

 

[kellstee]

This was such a great idea. I was just doing the same thing. . .writing multiple updates.

I'm updating old inventory titles. If the inventory name CONTAINS "Widget" I want to update it to "Gizmo".

So I have my base table (Data) with the old inventory name and a field for new inventory name. I set up a mapping table (Item Mapping) with an entry for the old name ("Widget") and the new name ("Gizmo")

However, one little problem I'm having is that the "Widget" title could have many different other words in it: "Widget XL", "Widget Small", "Get your Widget today"

So I want my query to look like this:
UPDATE Data AS D INNER JOIN [Item Mapping] AS M ON D.[Old] Like '*[Inventory Keyword]*' SET D.Item = M.Item

However, it doesn't like my join being a "Like" statement. Any ideas?