Menu
I just started learning SQL so I'm not that adept at it yet and I wasn't really sure how to do my research on this one because I couldn't word the question properly so I decided to ask a question for myself.
Alright, so here's what I'm trying to do in my particular case: I use Microsoft Access 2010. In my table called "MEMBERS" I have a field called "Address". The entries in this column are really messy so I'm trying to tidy it up. Some of the entries contain 'Road' while others contain 'Rd'. What I want to do is update the columns that have 'Road' in them and change that word only to 'Rd' without affecting the rest of the address.
So if one of the entries were '7 Example Road' I would like to create a query to change it to '7 Example Rd'.
This is the query I tried to use:
UPDATE MEMBERS
SET 'Rd'
WHERE Address LIKE '*Road'
Any help would be greatly appreciated!
920
You can think of update queries as a powerful form of the Find and Replace dialog box. You cannot use an update query to add new records to a database, or to delete records from a database. To add new records to a database you use an append query, and to delete whole records from a database you use a delete query.
The SQL UPDATE Query is used to modify the existing records in a table. You can use the WHERE clause with the UPDATE query to update the selected rows, otherwise all the rows would be affected.
The SQL LIKE OperatorThe LIKE operator is used in a WHERE clause to search for a specified pattern in a column. There are two wildcards often used in conjunction with the LIKE operator: The percent sign (%) represents zero, one, or multiple characters. The underscore sign (_) represents one, single character.
An SQL UPDATE statement changes the data of one or more records in a table. Either all the rows can be updated, or a subset may be chosen using a condition. The UPDATE statement has the following form: UPDATE table_name SET column_name = value [, column_name = value ...]
Try using REPLACE() function.
UPDATE MEMBERS
SET Address = REPLACE(Address, 'Road', 'Rd')
WHERE Address LIKE '*Road'
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
