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

Data Encryption

Data encryption is the process of scrambling stored or transmitted information so that it is unintelligible until it is unscrambled by the intended recipient. The intended recipient can then decode (or decrypt) the information. PHP offers multiple means to make this happen. However, none of these solutions are very effective without the applications running on secure servers and connections. The following are a list of the more common encryption functions in PHP: • md5() - MD5 is a third-party hash algorithm that PHP can use to create a digital fingerprint of a piece of data. It is next to impossible to (efficiently) recover the original text when a piece of data has been encrypted with the md5 hash algorithm. It is also vastly unlikely that any different text string will create an identical hash - a 'hash collision'. These properties make hashes ideally suited for storing an application's passwords because although an attacker may compromise a part of the system...
2 comments
Read more

Delimiting PHP Code

                           PHP was originally designed to be used in conjunction with a web server, and in the case of the LAMP architecture, the Apache Web Server.  PHP applications are designed embedding PHP scripts within a web page along with its HTML.  Unlike standard HTML pages which are sent directly from the web server to the end user, PHP files are first interpreted by the PHP application which then converts the PHP script into another form for display.  This process eliminates the end user from being able to see the original PHP script that was embedded in the HTML and provides  true interaction in HTML files.  This process is similar to proprietary applications such as ASP and Coldfusion; however, PHP is Open Source and cross- platform. PHP Tags             PHP scripts are distinguished from the HTML scripts by using delimiting characters ...
1 comment
Read more