There may be a requirement where existing data in a MySQL table needs to be modified. You can do so by using SQL UPDATE command. This will modify any field value of any MySQL table.
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 altogether.
- 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 the 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); ?>
Comments
Post a Comment