SQL UPDATE






So far we only looked at retrieving data from SQL database, but we never talked about modifying/updating data. The SQL UPDATE command is used to modify data stored in database tables.

If you want to update the email of one of the users in our Users table, youíll use a SQL statement like the one below:

UPDATE Users
SET Email = 'new_email_goes_here@yahoo.com'
WHERE Email = 'sgrant@sgrantemail.com'

Letís examine the statement above. The first row has the keyword UPDATE followed by the name of the table we are updating. The second row is the row that defines the changes made to the database fields using the keyword SET followed by the column name, equal sign and the new value for this column. You can have more than one assignment of new value after the SET keyword, for example if you want to update both the email and the city you will use the SQL statement below:

UPDATE Users
SET Email = 'new_email_goes_here@yahoo.com', City = 'San Francisco'
WHERE Email = 'sgrant@sgrantemail.com'

The third line is our WHERE clause, which specifies which record(s) to update. In our case it says to update the Email filed of the row having email sgrant@sgrantemail.com.

What happens if you remove the WHERE clause and your SQL query looks like this:

UPDATE Users
SET Email = 'new_email_goes_here@yahoo.com'

The answer is that all Email entries in the Users table will be changed to new_email_goes_here@yahoo.com. Most likely you will not want to do something like this, but you might have a case when you need to update several table rows at once. For example if one of the companyís offices has been moved from San Francisco to Los Angeles you might want to update all users with City San Francisco to Los Angeles (we assume that the employees have moved too). To do that, use the following SQL statement:

UPDATE Users
SET City = 'Los Angeles'
WHERE City = 'San Francisco'

In both UPDATE example having WHERE clause above, Iíve changed a table field to new value, using the same field in the WHERE clause criteria. This was purely coincidental and you can update different field(s) than the one used in your WHERE criteria, for example:

UPDATE Users
SET Email = 'new_email_goes_here@yahoo.com'
WHERE FirstName = 'Stephen' AND LastName = 'Grant'

When updating make sure that the WHERE clause criteria you have specified updates only the rows you want. Using the example above if you didnít have FirstName = ĎStephení in your WHERE criteria, you would have updated 2 records (Susan Grant and Stephen Grant), because both these users have one and the same last name.


SQL Tutorial

SQL Tutorial
SQL Introduction
RDBMS and Tables
SQL SELECT
SQL WHERE
SQL INSERT
SQL DISTINCT
SQL UPDATE
SQL DELETE
SQL ORDER BY
SQL Aggregate Functions
SQL GROUP BY
SQL HAVING
Relations, Keys, and Normalization
SQL JOIN
SQL UNION
SQL Nested Queries
CREATE DATABASE, CREATE TABLE and ALTER TABLE
SQL Views
SQL Indexes
SQL Training
SQL Hosting
SQL Replication
Contact Us
Legal
SQL Resources
Copyright © Art Branch Inc
SQL Tutorial | Bookmark | Contact | Link