Updating and deleting data with SQL |
There are however, times when the data stored in those entities need to be updated or erased all together. This posting will discuss ways of achieving this using a universal database language called SQL (for Structured Query Language). Using the previous example, somebody comes in to borrow a book; we record their name and the date on which the book was borrowed. Even though we could record the expected return date at the same time, the whole process would be flawed if somebody returned the book early, in which case our records would be showing that the book is out when it had been returned. Another situation could be when the borrower returns the book on a later date than expected; our records would be showing that the book is in stock when it is still out. To avoid this, we would only populate the date borrowed and expected return date, but leave the actual return date field empty until such a time when the book is returned. Once the book is returned, we would then update our records to reflect the accurate status of the book. Let us assume that a borrower named Charles borrowed a book on April 19th 2009 and is now returning the book. In SQL, bellow is the command/statement that we would issue to update the record: Update borrowing Explanation: To update a record, the statement has to start with the keyword “Update” followed by the table that you wish to update. Then you specify the column(s) for which you would like to set a new value (This could be a list of columns separated by commas. The “Where” clause restrict the action to be performed only to rows that meet the criteria following the “where” keyword. In this case, we are setting the return date to the current date on a record that has the values “Charles” as borrower ANDApril 29th 2009 as borrow date. If we were to delete that record, say in case where the borrower called Charles who borrowed the book had disappeared and we no longer have the interest in keeping the record. We would then issue the following statement.
Note that if we omitted the where clause, the system will delete or update all records in the specified table. So, if this is not what you wish to accomplish, make sure you specify the records to update or delete in the where clause |
RELATED ARTICLES:
Introduction to Accounting
Introduction to Operations Management
Operations management encompasses the process of how businesses produce goods and/or services. This involves responsibilities, activities and decisions of those tasked with managing production processes. Almost everything we use, eat or wear is made somewhere, possibly a manufacturing plant or simil
Common SQL Data types
Now that you have identified your data entities or tables, you have to define each attribute of the tables you have identified. These definitions include the name of the attribute and the type of data that can be stored in the field. Most relational database systems have a number of data types that
Creating database tables with SQL
To create a table in SQL, you use a command that requires the name of the table as well as names and data types of the fields of that table. In general, the syntax for creating a table is as follows: CREATE table_name ( column_name data_type); The table nam
Database Design
After gathering and analyzing your requirements, and depending on whether or not your application needs a database to store information; database design is the next big thing. This comes even before you start coding the application program. The reason for this is that, the application program
Internet for beginners
What is the Internet? The Internet" refers to the worldwide network of interconnected computers, that use a common protocol known as TCP/IP to communicate with each other. Every personal computer, web server, cell phone or other device that people
Introduction to HTML
HTML is a computer language used to create websites. The fact that you are looking at this page, means you are also looking at the outcome of html code. It is relatively easy to learn, with the majority of what you really need to know being readily available and for free on the internet.
Introduction to Programming
What exactly is programming? Programming can be defined as the art of telling a computer to do whatever you want it to. If you are reading this posting you probably started off with switching the computer on and then opened the browser that got you to this page. Every comp
Introduction to SQL
The term SQL stands for structure query language. It consists of a set of command that humans use to instruct a computer to store, modify and retrieve information. This posting and a few more to come will discuss the commands used to create/allocate storage space, how data is stored,
Selecting data from multiple tables
So far we have covered methods of retrieving data from single table. In real life however, you hardly get to only retrieve data from one table. In other words, to retrieve and interpret information, you normally get such information from different tables each of which will contain only information a