How to Removing Data - MySQL Delete Statement

[bahattab]

SQL Delete Statement - Removing Data


Often you need to remove old information from your database. You should be very careful when doing this because once it is gone, it's gone. That being said, when you are in phpMyAdmin, you can remove information a number of ways. First select the database on the left.

One way to remove entries is by then choosing the browse tab on the right. Next to each entry you will see a red X. Clicking the X will remove the entry, or to delete multiple entries you can check the boxes on the far left and then hit the red X at the bottom of the page.


Another thing you can do is click the search tab. Here you can perform a search. Let's say the doctor in our example database gets a new partner who is a pediatrician. He will no longer be seeing children, so anyone under 12 needs to be removed from the database.



You can preform a search for an age less than 12 from this search screen. All the results are now displayed in the browse format where you can delete individual records with the red X, or check multiple records and click the red X at the bottom of the screen.





Removing data by searching from a query window or command line is very easy, but please be careful:




DELETE FROM people WHERE age < 12



If the table is no longer needed you can remove the entire table by clicking on the "Drop" tab in phpMyAdmin or running this line:

DROP TABLE people






http://php.about.com/od/learnmysql/ss/mysql_data_4.htm


(http://php.about.com/od/learnmysql/ss/mysql_data_4.htm)

[bahattab]

MySQL UPDATE Query




There may be a requirement where existing data in a MySQL table need 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 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);
?>



http://www.tutorialspoint.com/mysql/mysql-update-query.htm