Skip to main content

The UPDATE Statement


The UPDATE statement modifies the contents of the existing records. To use it, name the table needing to be updated, provide a SET clause that lists one or more column value assignments, and optionally specify a WHERE clause that identifies which records to update. The Update statement uses the following general syntax;

UPDATE table_name SET column=expression(s) WHERE condition [options];

For example, to update the Country table by doubling the Population column for every country, the following statement would be used;

mysql> UPDATE Country 
          -> SET Population = Population * 2;

Query OK, 232 rows affected, 1 warning (#.## sec)

Rows matched: 239 Changed: 232 Warnings: 1

Note: The above statement returns a warning and completes the update for only most of the rows, but not all. This is because some of the updated numbers exceed the number of characters allowed for that column.
The effects of column assignments made by an UPDATE are subject to column type constraints, just as they are for an INSERT or REPLACE. By default, if an attempt to update a column to a value that doesn't match the column definition is made, MySQL converts or truncates the value. If strict SQL mode is enabled, the server will be more restrictive about allowing invalid values.It's possible for an update to have no effect. This can occur under the following conditions:

  •  When it matches no records for updating; due to an empty table or if no records match the WHERE clause.
  • When it does not actually change any column values (i.e. the value given is the same as the existing value).With respect to handling of records with unique key values, UPDATE is similar to REPLACE in some ways,but the two aren't equivalent:
  • UPDATE does nothing if there is no existing record in the table that contains the specified key values. REPLACE does not require an existing record with the key values and adds one if none exists.
  • UPDATE can be used to change some columns in an existing record while leaving others unchanged.

REPLACE entirely discards the existing record. To achieve the effect of leaving some columns
unchanged with REPLACE, the new record must specify the same values in those columns that the existing record has.

UPDATE by default makes no guarantee about the order in which rows are updated. This can sometimes result in problems. Suppose that the people table contains two rows, where id is a primary key:

mysql> SELECT * FROM people;

+--------------------+
| id | name | age |
+--------------------+
| 2 | Victor | 21    |
| 3 | Susan | 15   |
+--------------------+

Labels:

Comments

Post a Comment

Popular posts from this blog

PHP INTRODUCTION

                     PHP  (recursive acronym for  PHP: Hypertext Preprocessor ) is a widely-used open source general-purpose scripting language that is especially suited for web development and can be embedded into HTML. PHP stands for  P HP:  H ypertext  P reprocessor PHP is a server-side scripting language, like ASP PHP scripts are executed on the server PHP supports many databases (MySQL, Informix, Oracle, Sybase, Solid, PostgreSQL, Generic ODBC, etc.) PHP is an open source software PHP is free to download and use Why PHP? PHP runs on different platforms (Windows, Linux, Unix, etc.) PHP is compatible with almost all servers used today (Apache, IIS, etc.) PHP is FREE to download from the official PHP resource:  www.php.net PHP is easy to learn and runs efficiently on the server side What can PHP do? Anything. PHP is mainly focused on server-side scripting, so you can...
6 comments
Read more

SQL Joins

1. Login in to the mysql client using the login name and password provided by your instructor (if not already logged in). 2. View the existing databases that the MySQL Server is maintaining. SHOW DATABASES; 3. Utilize the photo_album database. USE photo_album; ---- If Need be, recreate the database and load the SQL data in that will be used for this lab CREATE DATABASE photo_album; SOURCE /tmp/photo_album.sql; 4. Query the database to display the active primary customer (email, first and last name) and all the associated active login names associated with that customer. SELECT customers.email_customer, customers.fname, customers.lname, logins.login_name  FROM customers, logins WHERE customers.email_customer = logins.email_customer  AND customers.active_customer = 'True'  AND logins.active_login = 'True';
Post a Comment
Read more

Self Joins

A table name is always ambiguous when table is joined to itself using a self-join. For example, the Country table in the world database contains an IndepYear column indicating the year in which each country achieved independence. To find all countries that have the same year of independence as some given country, a self-join can be used. However, the query cannot be written like this: mysql> SELECT IndepYear, Name, Name -> FROM Country JOIN Country -> ON IndepYear = IndepYear AND Name = 'Qatar'; ERROR 1066 (42000): Not unique table/alias: 'Country' Furthermore, the ambiguity cannot be removed from column references by preceding them with table name qualifiers because the names remain identical: mysql> SELECT Country.IndepYear, Country.Name, Country.Name -> FROM Country JOIN Country -> ON Country.IndepYear = Country.IndepYear -> AND Country.Name = 'Qatar'; ERROR 1066 (42000): Not unique table/alias: 'Country' It ...
Post a Comment
Read more