|
Imagine that you have a MySQL table that holds the information of all the employees in your company. One of the columns in this table is called "Seniority" and it holds an integer value of how many months an employee has worked at your company. Unfortunately for you, your job is to update these numbers every month.
A D V E R T I S E M E N T
You may be thinking that you'll have to open up your MySQL administration tool and edit each entry by hand. That would take hours. On the other hand, you could master MySQL and have an automated script that you run each month to get the job done for you.
Once again we will be working with the data from a previous example. Sandy has just had a birthday and she now 22 years old. Our job now is to update her age using MySQL commands like UPDATE, SET, and WHERE.
UPDATE - Performs an update MySQL query
SET - The new values to be placed into the table follow SET
WHERE - Limits which rows are affected
Syntax:
Here is generic SQL syntax of UPDATE command to modify data into MySQL table:
UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]
|
- You can update one or more field all together.
- You can specify any condition using WHERE clause.
- You can update values in a single table at a time.
The WHERE clause is very useful when you want to update selected rows in a
table.
Updating Data from Command Prompt:
This will use SQL UPDATE command with WHERE clause to update selected data
into MySQL table tutorials_tbl
Example:
Following example will update tutorial_title field for a record having
tutorial_id as 3.
root@host# mysql -u root -p password;
Enter password:*******
mysql> use TUTORIALS;
Database changed
mysql> UPDATE tutorials_tbl
-> SET tutorial_title='Learning JAVA'
-> WHERE tutorial_id=3;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql>
|
Updating Data Using PHP Script:
You can use SQL UPDATE command with or without WHERE CLAUSE into PHP function
mysql_query(). This function will execute SQL command in similar way it
is executed at mysql> prompt.
Example:
Try out following example to update tutorial_title field for a record
having tutorial_id as 3.
<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
die('Could not connect: ' . mysql_error());
}
$sql = 'UPDATE tutorials_tbl
SET tutorial_title="Learning JAVA"
WHERE tutorial_id=3';
mysql_select_db('TUTORIALS');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
die('Could not update data: ' . mysql_error());
}
echo "Updated data successfully\n";
mysql_close($conn);
?>
|
|
