UPDATE QUERY
We continue the article on the query SQL (Structured Query Language) in MS Access in the first event where we talked about the select query with this new article in which we will discuss the update query.
First, remember that each time you run a query in SQL, the changes in database tables are irreversible, so before you update the data with a new query would be good to save the data, then when we tested the procedure can operate without a backup.
The syntax for the SQL update command is:
UPDATE [table name] SET [field] = x WHERE …
so if for example we have the list of State and we want to change your ZIP code to 00111 for all those who have the same ZIP code 00100, which are many, we write:
UPDATE [State] SET [Zip] = ‘00111’ WHERE [ZIP] = ‘00100’
Another example would be to update the sales price of each item that starts with “B” with a surcharge of 2%:
UPDATE [Items] SET [price] = [Price] + ([price] * 0.02) WHERE [Description] LIKE ‘B *’
To create this type of query in Access simply select the “Queries” -> “Query update”, then choose the field to modify and then enter the new value and the filter, then view the data sheet and see the data are moficare and finally execute the query with the button showing a red exclamation mark.
DELETE QUERY
The syntax of this query is similar to previous
DELETE FROM [Table] WHERE ….
so if for example you want to delete all af data from the “States” table that were foreign (usually code EE) we run this query:
DELETE FROM [State] WHERE [Code] = ‘EE’
Access graphically create this type of query is similar to the above only instead of update query must select Delete Query from the menu.
As always for more information or contact me you can do this in our forum dedicated to the database.