How do I make this a select statement? I want it to look in the entire table and find something and replace it with something else

Question

How do I make this a select statement? This is for Microsoft access. I want it to look everywhere and in any part of any cell in an entire table and find "Coal 104" and replace it with "Lava 104"

Answer 1

Off the top of my head.

Update myTable SET cell1 = Replace(cell1, 'Coal 104', 'Lava 104'), -- repeat for all cells in table
-- optional, but may run faster
WHERE cell1 LIKE '*Coal 104*' OR -- repeat for all cells in table

In case it is necessary to say, only do this to the VARCHAR fields.

Answer 2

You could do it with VBA code. You'd need to iterate through all the tables in your database, and for each table, iterate through all the fields.

With each field, create a sql statement to update the rows containing your search value and replace it with your target value. Then execute your dynamic sql statement.

Answer 3

Since it is not entirely clear if you want to update the values in the table or if it is ok to just show the new values I elaborate (this might even be useful to better understand the process).

This is the table 'mytable' containing the input values in cell1

**cell1**
abc Coal 104 efg
abcdefg
othervalue
Coal 104 xyz
more

If you use the graphical query editor in MS Access to show all the values (no replace yet) Access would create the following SQL-stamtement (right click in the editor window an select 'SQL-View'):

SELECT myTable.cell1
FROM myTable;

(nothing special really) Now going back to the graphical editor window you could could insert a second value, e.g. cell1_new that shows the replaced value (this might be useful for 'debugging' in any case) and you get (again in the SQL-view)

SELECT myTable.cell1, Replace([cell1],"Coal 104","Lava 104") AS cell1_new
FROM myTable;

runnig the query I see:

**cell1**          **cell1_new**
abc Coal 104 efg     abc Lava 104 efg
abcdefg              abcdefg
othervalue           othervalue
Coal 104 xyz         Lava 104 xyz
more                 more

(just to be clear, this does not update the values in the original table, it is just a view).

Using the graphical query editor you can even construct UPDATE-queries. First change the query-type to 'Update query' (i am not sure that is what it is called in English MS Access). Secondly insert your replace-'formula' that was used to show the values for 'cell1_new' to the line Update just below cell1 an myTable (without the 'cell1_new:'). Delete the formula for this column cell1_new (as we now Update only the values in column cell1) Third, as it was suggested you could restrict the values that get updated to only the lines that contain "Coal 104", so enter "Coal 104" in the criteria-line.

In the grafical editor it should look more or less like this:

Field  cell1
Table  myTable Update
Replace([cell1],"Coal 104","Lava 104")
crieteria Like "*Coal 104*"

Again checking the SQL-Statement (which I find easier to understand than the grafical view) now read like this:

UPDATE myTable SET myTable.cell1 = Replace([cell1],"Coal 104","Lava 104")
WHERE (((myTable.cell1) Like "*Coal 104*"));

(I left those annoying brakets in the WHERE-Statement Access puts in so it looks the same).

To finish, run this query (e.g. press the exclamation mark). Normally (except you changed this behavior in settings) Access asks you if you really want to update -- in this example -- two values.

I hope this helps to get an idea, how to use the grafical editor to experiment with different queries. I think in that respect Access is not a bad learning tool.

HTH Andreas