JavaScript Editor Js editor     Website development 
↑


Main Page

Previous Page
Next Page

Updating Data

Once your tables contain some data, you have the option of changing existing records. The most frequent reason for doing this is if information has been entered incorrectly. Or, 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 columns is

UPDATE tablename SET column=value

You can adjust multiple columns at one time, separating each from the next by a comma.

UPDATE tablename SET column1='value', column2='value2'...

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 column2='value2

Updates, along with deletions, are one of the most important reasons to use a primary key. This number, which should never change, can be a reference point in WHERE clauses, even if every other field needs to be altered.

To update records:

1.
Find the client ID and name of any client without a phone number listed (Figure 5.23).

SELECT client_id, client_name FROM clients WHERE client_phone IS NULL;

In order to perform the update, I'll need to know the primary key of the record.

Figure 5.23. Prior to updating a record, you must know what the primary key is for that record. Then you'll have a reference point for your UPDATE query (Figure 5.24).


2.
Update the phone number for a specific client (Figure 5.24).

UPDATE clients SET client_phone = '(800) 123-4567' WHERE client_id = 2;

When I originally entered this client, I did not include this information. Using UPDATE, I can always go back in and assign this value later.

Figure 5.24. The UPDATE SQL command is an easy way to alter existing data in your tables.


3.
Confirm that the update worked (Figure 5.25).

SELECT * FROM clients WHERE client_id=2 \G

Figure 5.25. A comparable SELECT query reflects the updated information.


Tips

  • Be extra certain to use a WHERE conditional whenever you use UPDATE unless you want the changes to affect every row.

  • 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.

  • You can apply a LIMIT clause to an update to make sure it doesn't erroneously update too many records:


UPDATE clients SET client_phone = '(800) 123-4567' WHERE client_id = 2 LIMIT 1

With this query it's unlikely or impossible that more than one record would be updated, but the extra insurance is nice.


Previous Page
Next Page


JavaScript Editor Js editor     Website development


©

→