| Once your tables contain some data, you have the option of changing existing records. The most frequent reason for doing this would be if information were entered incorrectlyor in the case of user information, if data gets changed (such as a last name or email address) and that needs to be reflected in the database. The syntax for updating records is UPDATE tablename SET column='value'
You can alter multiple columns of one record at a single time, separating each from the next by a comma. UPDATE tablename SET column1='value',  Normally you will want to use a WHERE clause to specify what rows to affect; otherwise, the change would be applied to every row. UPDATE tablename SET column1='value' WHERE Updates, along with deletions, are one of the most important reasons to use a primary key. This numberwhich should never changecan be a reference point in WHERE clauses, even if every other field needs to be altered. To update records 1. | Determine which record will be updated (Figure 4.26).
SELECT user_id FROM users WHERE first_name = 'Michael AND last_name='Chabon;
Figure 4.26. Before updating a record, determine which primary key to use in your WHERE clause. 
In my example, I'll change the email for this author's record. To do so, I must first find that record's primary key, which this query accomplishes.
| 2. | Update the record (Figure 4.27).
UPDATE users SET email= 'mike@authors.com WHERE  user_id = 17;
Figure 4.27. This query altered the value of one column in just one row. 
To change the email address, I use an UPDATE query, being certain to specify to which record this should apply, using the primary key (user_id). MySQL will report upon the success of the query and how many rows were affected.
| 3. | Confirm that the change was made (Figure 4.28).
SELECT * FROM users WHERE user_id=17;
Figure 4.28. As a final step, I confirm the update by selecting the record again. 
Although MySQL already indicated the update was successful (see Figure 4.27), it can't hurt to select the record again to confirm that the proper changes occurred.
|
 Tips
Be extra certain to use a WHERE conditional whenever you use UPDATE unless you want the changes to affect every row. If you run an update query that doesn't actually change any values (like UPDATE users SET first_name='mike' WHERE first_name='mike'), you won't see any errors but no rows will be affected. To protect yourself against accidentally updating too many rows, apply a LIMIT clause to your UPDATEs. UPDATE users SET email= 'mike@authors.com WHERE user_id = 17 LIMIT 1
You should never have to perform an UPDATE on the primary-key column, because this value should never change. Altering a primary key in one table could destroy the integrity of a relationship with another table.
|
