In previous SQL tutorials, I’ve demonstrated tricks for using the SELECT command to find and display data stored in your SQL tables. Once you've learned how to find data from the SQL command line, you'll be ready to try adding new records or changing the information in existing records. This week, I'll explain how to do this using the INSERT and UPDATE commands.
Take these keywords seriously
My other SQL lessons dealt exclusively with viewing data. The ability to query a table provides the help desk pro with a powerful troubleshooting tool. The best part of querying is that it’s almost impossible to mess up a database when all you’re doing is mining it for information.
Before I discuss INSERT and UPDATE, let me make one thing perfectly clear: You can do a lot of harm with these commands because they affect your data.
In general, only database administrators (DBAs) should manually insert records or make changes to existing records in a production database. Ideally, adding new records and changing existing records are tasks you'd accomplish using the appropriate application interface—not through the SQL command line.
However, adding and updating records is a common part of development and quality assurance testing. And level 2 and level 3 support folks usually have the authority to resolve some problems by making minor adds and changes to a database, with prior approval by the DBA.
Let's assume you have the permissions required to make adds or changes. You must keep in mind that some databases are designed with constraints and triggers in place that may prevent you from inserting or updating anything. Here are some of the issues to remember:
- Index key fields—When you add a record to an indexed table, you must make sure to create an acceptable value for any index key fields. For example, you cannot set a null value to a key index field. If no duplicates in the key field are allowed, you'll need to make sure the record you insert doesn't contain a value that already exists.
- Foreign key fields—A foreign key field identifies a relationship between two tables. When you insert a new record or change an existing value, you must make sure to populate the foreign key with an appropriate value.
- Null values—Nullable fields can contain no value, which means data entry clerks can leave that field empty. Some fields are not nullable, however. If you attempt to insert a record without populating a field that isn’t nullable, the SQL engine will generate an error message and reject your changes.
Using the INSERT command
Assuming you have permission to insert records in a table, and you’re aware of any constraints on the data that goes into those new records, here’s the basic syntax for the INSERT command:
INSERT INTO tablename (column1,column2) VALUES (value1,value2)
You replace column1 and column2 with the names of the columns (or fields) from your table. You replace value1 and value2 with the corresponding values for those columns.
To demonstrate, suppose you want to add a new record to a table named Products, and you have three fields to populate: Name, Code, and Price. Your INSERT command would look like this:
INSERT INTO Products (Name,Code,Price) VALUES ("Bucket","B201",49.99)
Notice that I used commas to separate the field names and the list of values. Also, I enclosed the two text entries in quotation marks. If you need to insert a numeral as text, be sure to enclose it in quotation marks.
The keyword INTO is optional, as is the list of field names. So, you could write that same instruction like this:
INSERT Products VALUES ("Bucket","B201",49.99)
Here's the rub. If you attempt to load a value into a text field, or vice versa, you’ll get an error and the record won’t be inserted. For that reason, I recommend that you always include the list of field names. When you list the fields, the values must be listed in the same order as their corresponding field names. So having the names visible in your command line helps you list the values in the right order.
If you omit the list of field names, Microsoft SQL Server 2000 will use the values you supply to populate the fields in the order in which the fields were created. Personally, I'd rather just code the fields myself than trust that I'll remember in what order the fields were created.
Some implementations of SQL, such as Microsoft SQL 2000, support an additional parameter for the INSERT command: the FROM clause. The FROM clause lets you pull the values for the new records from a table.
Using the UPDATE command
The UPDATE command lets you make changes to existing records. In its simplest form, the UPDATE command looks like this:
UPDATE tablename SET fieldname=value WHERE some condition is true
To illustrate, suppose you want to change the area code from 317 to 765 for all of your customers whose ZIP code is 46135. Assuming your records are in the Customers table, you might issue an UPDATE command in this form:
UPDATE Customers SET Areacode = "765" WHERE Zipcode="46135"
That command has a pretty broad scope. It will reset the entry in the Areacode field for every record in the table where the Zipcode field contains 46135. To narrow the scope of your UPDATE statements, you can read this column on how to fine-tune WHERE clauses.
As with any action that changes business data, use the UPDATE command carefully. Have someone check your statement before you execute it just to make sure you haven't made a critical typo.
Scratching the surface
In this column, I've demonstrated only a few of the ways you can use INSERT and UPDATE to maintain your SQL tables. In future columns, I'll show you some advanced techniques for each of these important keywords. In the meantime, use them with care.
What's your SQL quotient?
Are these beginner-level SQL lessons helping? Do you want more? Please let us know by posting a comment below or writing to Jeff.
Jeff Davis has written more than 1,000 technical and career-related articles for TechRepublic. He works as a freelance technical writer and database developer.